package com.scalar.chiptrack.sales.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.alarms.Alarm;
import com.scalar.chiptrack.alarms.AlarmUser;
import com.scalar.chiptrack.alarms.Comment;
import com.scalar.chiptrack.alarms.dao.AlarmDAO;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.Labels;
import com.scalar.chiptrack.commons.ClientInfo;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.FinishedGoods;
import com.scalar.chiptrack.operations.process.dao.FinishedGoodsDAO;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.users.dao.NotificationSetupDAO;
import com.scalar.chiptrack.users.dao.UserDAO;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.sales.SalesOrder;
import com.scalar.chiptrack.sales.ContactPerson;
import com.scalar.chiptrack.sales.LineItem;
import com.scalar.chiptrack.sales.CustomerPartNumber;
import com.scalar.chiptrack.sales.Customer;
import org.apache.struts.util.LabelValueBean;
import org.apache.struts.util.MessageResources;

import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.sql.Types;
import java.text.Format;

import java.util.Date;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.HashMap;
import java.util.Calendar;
import java.util.StringTokenizer;
import java.util.NoSuchElementException;

/**
 * Implementation of <strong>DAO</strong> business logic for
 * search with sales order
 *
 * @author

 * @version $Revision: 1.57 $ $Date: 2011/04/25 20:00:08 $
 */


public class SalesOrderDAO implements Constants
{
   public static final String FINANCE_HOLD = "FINANCE";
   public static final String MARKETING_HOLD = "MARKETING";
   public static final String OPERATIONS_HOLD = "OPERATIONS";
   public static final String COOL_STATUS = "Cancellation needs approval";

   public SalesOrderDAO() { }

   /**
    * Get the SalesOrder details..
    * @return salesOrder
    */
   public static SalesOrder getSalesOrderCustomerDetails ( String customerId ) throws ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       SalesOrder salesOrder = null;
       int customerIdFromDB = -1;
       int shippingAddressIdFromDB = 0;
       int billingAddressIdFromDB = 0;

       try
       {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.selectCustomer" ) );
            pstmt.setInt ( 1, Integer.parseInt ( customerId ) );

            rs = pstmt.executeQuery ();
            if ( rs.next () == false )
            {
                //Customer for SalesOrder not found, ???Throw exception
            }
            else
            {
                salesOrder = new SalesOrder ();

                customerIdFromDB = rs.getInt ( "Customer_ID" );
                salesOrder.setCustomerId ( customerIdFromDB );
                salesOrder.setCustomerName ( rs.getString ( "C_Name" ) );
                salesOrder.setContactPerson ( rs.getString ( "C_Contact_Person" ) );
                String shippingMethod1 = rs.getString ( "C_Shipping_Method_1" );
                String shippingMethod2 = rs.getString ( "C_Shipping_Method_2" );
                String shippingMethod3 = rs.getString ( "C_Shipping_Method_3" );
                String shippingAccount1 = rs.getString ( "C_Act_Number_1" );
                String shippingAccount2 = rs.getString ( "C_Act_Number_2" );
                String shippingAccount3 = rs.getString ( "C_Act_Number_3" );
                salesOrder.setRegionalRep ( rs.getString ( "C_Rep_Office" ) );
                salesOrder.setSalesPerson ( rs.getString ( "C_Sales_Person" ) );
                salesOrder.setShippingInstructions ( rs.getString ( "C_Shipping_Instructions" ) );

                shippingAddressIdFromDB = rs.getInt ( "C_Default_Shipping_Address_Id" );
                billingAddressIdFromDB  = rs.getInt ( "C_Default_Billing_Address_Id" );
                salesOrder.setSalesRep ( rs.getString( "C_Sales_Rep" ) );
                salesOrder.setShippingInstructionsId( rs.getInt("C_SHIPPING_INSTRUCTIONS_ID" ) );
                salesOrder.setIsConsignee ( rs.getInt ( "C_IS_CONSIGNEE" ) == 0  );
                salesOrder.setIsConsignmentSales ( rs.getInt ( "C_IS_DEFAULT_CONSIGNMENT") == 0 ? "0" : "" ); //if zero setting default selected if not user has to select manually
                salesOrder.setShippingTerms( rs.getString ( "C_PAY_TERMS" ));
                salesOrder.setDeliveryTerms( rs.getString ( "DELIVERY_TERMS" ) );
                salesOrder.setShippingAddressId ( shippingAddressIdFromDB );
                salesOrder.setBillingAddressId ( billingAddressIdFromDB );
                salesOrder.setPhoneNumber( "" );
				salesOrder.setCellNumber( "" );
				salesOrder.setFaxNumber( "" );
				salesOrder.setEmail( "" );

                salesOrder.setShippingAccount ( shippingAccount1 );
                salesOrder.setShippingMethodName ( shippingMethod1 );
                if( conn != null )
                {
                    conn.close();
                    conn = null;
                }

                Address address = CustomerDAO.getBillingAddress ( salesOrder.getBillingAddressId () );
                if ( address != null )
                {
                    salesOrder.setBillingContactPerson( address.getContactPerson() );
                    salesOrder.setBillingAddressAsObject ( address );
                    salesOrder.setBillingAddress ( address.getAddressAsString () );
                }
                //Get the default Shipping address
                Address shipAddress = CustomerDAO.getAddressWithContactPersonDetails ( salesOrder.getShippingAddressId () );
                //To get shipping Contact person
                if ( shipAddress != null )
                {
                    salesOrder.setShippingContactPerson(shipAddress.getContactPerson());
                    salesOrder.setShippingAddress ( shipAddress.getAddressAsString() );
                    salesOrder.setShippingAddressAsObject ( shipAddress );
                }
                //Set all the shippingAddresses in the salesOrder
                ArrayList shippingList = new ArrayList ();
                NewSalesOrderDAO.loadCustomerAddressesOfType(shippingList, salesOrder.getCustomerId (), Constants.ADDRESS_TYPE_SHIPPING);
                salesOrder.setShippingAddresses ( shippingList );

                ArrayList billingList = new ArrayList ();
                NewSalesOrderDAO.loadCustomerAddressesOfType(billingList, salesOrder.getCustomerId (), Constants.ADDRESS_TYPE_BILLING);
                salesOrder.setBillingAddresses ( billingList );

                ArrayList shippingMethods = null;
                shippingMethods = CustomerDAO.loadSalesOrderShippingMethods ( customerIdFromDB );//To get all shipping methods of customer
                salesOrder.setShippingMethods ( shippingMethods );

                salesOrder.setContactPersons(CustomerDAO.getAllContactPersonsForCustomer( customerIdFromDB ));
                salesOrder.setContactPersonsForBillingAddress(CustomerDAO.getAllContactPersonsForAddress(  billingAddressIdFromDB));
                salesOrder.setContactPersonsForShippingAddress(CustomerDAO.getAllContactPersonsForAddress( shippingAddressIdFromDB));

                String contactPersonName = salesOrder.getContactPerson();
				ArrayList contactPersons = salesOrder.getContactPersons();
				contactPersons.add(salesOrder.getCustomerName());
				salesOrder.setContactPersons(contactPersons);
				salesOrder.setAllShippingTerms( SalesDAO.getAllShippingTerms() );
                salesOrder.setAllDeliveryTerms( SalesDAO.getAllDeliveryTerms( null ) );
				if(contactPersonName != null &&  contactPersonName.trim().length()!= 0)
				{
					ContactPerson contactPerson = CustomerDAO.getContactPersonDetails (customerIdFromDB , salesOrder.getContactPerson());
					if(contactPerson != null)
					{
					    salesOrder.setContactPerson( contactPerson.getContactPerson() );
						salesOrder.setPhoneNumber( contactPerson.getPhoneNumber() );
						salesOrder.setCellNumber( contactPerson.getCellNumber() );
						salesOrder.setFaxNumber( contactPerson.getFaxNumber() );
						salesOrder.setEmail( contactPerson.getEmail() );
						salesOrder.setAddress( contactPerson.getAddress() );
					}
                    else if(contactPerson == null)
					{
						salesOrder.setContactPerson( "" );
						salesOrder.setPhoneNumber( "" );
						salesOrder.setCellNumber( "" );
						salesOrder.setFaxNumber( "" );
						salesOrder.setEmail( "" );
						salesOrder.setAddress( "" );
					}

				}
				else if(contactPersonName == null || contactPersonName.trim().length()== 0 )
				{
					salesOrder.setCellNumber ("");
					salesOrder.setAddress("");
				}
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get Sales Order Info from database" , sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }
        return salesOrder;
   }

   /**
    * Save the ShippingAddress Details
    *
    * @param address1 , address2, City, State, ZipCode, Country, Phone Number, Fax Number, email
    * @param tagName
    * @return AddressID
    */
    public static int saveShippingAddress(DBTransaction dbTransaction, String address1, String address2, String address3, String address4, String city, String state, String zipCode, String country, String phoneNumber, String faxNumber, String email, String contactPerson, String cellNumber, String tagName) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int addressId = -1;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.address.insertAddress" ) );

            pstmt.setString ( 1, address1 );
            pstmt.setString ( 2, address2 );
            pstmt.setString ( 3, city );
            pstmt.setString ( 4, state );
            pstmt.setString ( 5, zipCode );
            pstmt.setString ( 6, country );
            pstmt.setString ( 7, phoneNumber );
            pstmt.setString ( 8, faxNumber );
            pstmt.setString ( 9, email );
	        pstmt.setString ( 10, contactPerson);//To show contact person in address details
			pstmt.setString ( 11, cellNumber);
			pstmt.setString ( 12, address3 );
			pstmt.setString ( 13, address4 );
			pstmt.setString ( 14, tagName );

            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;
			pstmt = conn.prepareStatement(SqlQueryMappings.get ( "vendor.getAddressId" ) );

            rs = pstmt.executeQuery();
            if ( rs.next() )
            {
                  addressId = rs.getInt( "address_id" );
            }
            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException ( "Could not update Address into database" , sqle );
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                     if ( conn != null )
                     {
                        if ( success == true )
                            conn.commit();
                        else
                            conn.rollback();
                     }
                }
                catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){}
            }
        }
        return addressId;
    }

    public static ArrayList getEndUsers() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

	    ArrayList<String> endUsersList = new ArrayList<String>();

        try
        {
            conn  = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "sales.endUsers.select" ) );
            rs = pstmt.executeQuery();

	        while( rs.next() )
	        {
                endUsersList.add(rs.getString("SO_EndUser"));
	        }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get Regions from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }
        return endUsersList;
   }

    public static ArrayList getSalesPersons() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

	    ArrayList<String> salesPersonList = new ArrayList<String>();

	    try
        {
            conn  = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "sales.salesPersons.select" ) );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                salesPersonList.add( rs.getString( "SO_Sales_Person" ) );
            }

        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get Regions from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try{ rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try{ pstmt.close(); } catch ( SQLException excp ) {}
            }
            if ( conn != null )
            {
                try{ conn.close(); } catch ( SQLException excp ) {}
            }
        }
        return salesPersonList;
   }

   /**
    * This method is added by Bhaskar Rao V on 16 May to get the status also transferred on other pages.
    * in change_sales_order.jsp. This method just contains one extra argument called String status
    */
    public static LineItem loadLineItemInfo(int customerId, int lineItem, String marketingPartNo,
                                            String manufacturingPartNo, String requestDate, int quantity,
                                            float unitPrice, String status, int isSample) throws ScalarException
   {

       LineItem lineItemInfo = null;
       //Check the partnumbers are correct or not, if they are get productId
       lineItemInfo = validatePartNumbers ( null, manufacturingPartNo );

       //If the partnumbers entered not valid and return the null object to set errors
       if ( lineItemInfo == null )
       {
           return lineItemInfo;
       }
       else
       {
           lineItemInfo.setLineItemNo ( lineItem );
           lineItemInfo.setQuantity ( quantity );
           lineItemInfo.setUnitPrice ( unitPrice );
           lineItemInfo.setRequestDate ( requestDate );
           lineItemInfo.setStatus ( status );
       }
       double price = loadLineItemPriceAsDouble ( null, lineItemInfo.getProductId (), customerId, quantity, requestDate, isSample);

       lineItemInfo.setAmount ( lineItemInfo.getUnitPrice () * lineItemInfo.getQuantity () );
       lineItemInfo.setUnitPriceAsDouble ( price );
       lineItemInfo.setAmountAsDouble ( lineItemInfo.getQuantity () * lineItemInfo.getUnitPriceAsDouble () );

       return lineItemInfo;
   }

   /**
    * Load all the line item information
    * @param customerId
    * @param isSample
    * @return LineItem
    */
    public static LineItem loadLineItemInfo(int customerId, int lineItem, String marketingPartNo,
                                            String manufacturingPartNo, String requestDate, String leadTime, int quantity,
                                            float unitPrice, int isSample) throws ScalarException
    {

            LineItem lineItemInfo = null;
            //Check the partnumbers are correct or not, if they are get productId
            lineItemInfo = validatePartNumbers( marketingPartNo, manufacturingPartNo );

            //If the partnumbers entered not valid and return the null object to set errors
            if(lineItemInfo == null)
            {
                return lineItemInfo;
            }
            else
            {
                lineItemInfo.setLineItemNo( lineItem );
                lineItemInfo.setQuantity( quantity );
                lineItemInfo.setUnitPrice( unitPrice );
                lineItemInfo.setUnitPriceAsDouble(unitPrice);
                lineItemInfo.setLeadTime(leadTime);
                lineItemInfo.setRequestDate( requestDate );
            }
            double price = loadLineItemPriceAsDouble(null,lineItemInfo.getProductId(),customerId,quantity,requestDate, isSample);

            lineItemInfo.setAmount( lineItemInfo.getUnitPrice() * lineItemInfo.getQuantity() );

            lineItemInfo.setUnitPriceAsDouble ( price );
            lineItemInfo.setAmountAsDouble( lineItemInfo.getQuantity() * lineItemInfo.getUnitPriceAsDouble() );

            return lineItemInfo;
    }

   /**
    * Update the lineItem for UnitPrice and Amount,
    *
    * @return ArrayList(lineItems)
    */
   // This method is not used for SalesOrderAction & ChangeOrderAction.
   // But is still used from ApproveOnlineOrderAction. - Prabhanjan (19 May 2004)
   private static float loadLineItemPrice( DBTransaction dbTransaction, int productId, int customerId, int quantity ) throws ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       float unitPrice;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getCustomerProductUnitPrice" ) );
            pstmt.setInt( 1, productId );
            pstmt.setInt( 2, customerId );
            pstmt.setInt( 3, quantity );

            rs = pstmt.executeQuery();

            if( rs.next() == false )
            {
                rs.close();
                rs = null;
                pstmt.close();
                pstmt = null;

                if( conn == null || conn.isClosed() )
                {
                    conn = DataSource.getConnection();
                }

                pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getStndProductPrice" ) );
                pstmt.setInt( 1, productId );
                pstmt.setInt( 2, quantity );
                rs = pstmt.executeQuery();
                //To get price of a lineitem which has quantity out of range.
                if( rs.next() == false )
                {
                    rs.close();
                    rs = null;
                    pstmt.close();
                    pstmt = null;
                    if( conn == null || conn.isClosed() )
                    {
                        conn = DataSource.getConnection();
                    }

                    pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getProductPrice" ) );
                    pstmt.setInt( 1, productId );
                    rs = pstmt.executeQuery();
                    if( rs.next() == true )
                    {
                        unitPrice = rs.getFloat( "Price" );
                    }
                    else
                    {
                        unitPrice = 0.0f; //Set sometning
                    }
                }
                else
                {
                    unitPrice = rs.getFloat( "PPT_Unit_Price" );

                }
            }
            else
            {
                unitPrice = rs.getFloat( "CPT_Unit_Price" );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Unit Price Info from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch ( SQLException excp ) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return unitPrice;
   }

    private static double loadLineItemPriceAsDouble(DBTransaction dbTransaction, int productId, int customerId, int quantity, String requestDate, int isSample) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        double unitPrice;

         try
         {
             if( dbTransaction == null )
             {
                 conn = DataSource.getConnection();
             }
             else
             {
                 conn = dbTransaction.getConnection();
             }
			 cstmt = conn.prepareCall( SqlQueryMappings.get("sales.salesOrder.getLineItemPrice") );
             cstmt.setInt   ( 1, productId );
             cstmt.setInt   ( 2, customerId );
             cstmt.setInt   ( 3, quantity );
             cstmt.setDate  ( 4, StringUtils.getSQLDate( requestDate ) );
             cstmt.registerOutParameter( 5, java.sql.Types.DOUBLE );
             cstmt.setInt   ( 6, isSample );
             cstmt.executeQuery();
             unitPrice = cstmt.getDouble(5);
         }
         catch( Exception sqlex )
         {
             sqlex.printStackTrace();
             throw new ScalarException( "Could not get Unit Price Info from database", sqlex );
         }
         finally
         {
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException excp ) {}
             }
             if (cstmt != null)
             {
                 try { cstmt.close(); } catch ( SQLException excp ) {}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException excp) {}
             }
         }
         return unitPrice;
    }



   public static String loadOldQtyPrice(DBTransaction dbTransaction, int productId, int customerId, int quantity, String requestDate, int isSample) throws ScalarException
   {
       // To use the new method which return double value instead of float as price field is now double type.
       return loadLineItemPriceAsDouble( null,productId,customerId,quantity, requestDate, isSample)+"";
   }

   // This method is only used in GP Actions but GP Actions are not used as of now. Just to avoid compilation errors while building.
   public static String loadOldQtyPrice(DBTransaction dbTransaction, int productId, int customerId, int quantity) throws ScalarException
   {
       //To use the new method which return double value instead of float as price field is now double type.
       return loadLineItemPriceAsDouble( null,productId,customerId,quantity, "", 1)+"";
   }

   private static LineItem validatePartNumbers( String marketingPartNo, String manufacturingPartNo ) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        LineItem lineItem = null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.salesOrder.getProductIdWithManufacturingPartNo" ) );
            pstmt.setString ( 1, manufacturingPartNo );

            rs = pstmt.executeQuery();
            if(rs.next() == false)
            {
                // No values are selected .. not valid part numbers
            }
            else
            {
                lineItem = new LineItem();
                lineItem.setProductId( rs.getInt( "Product_Id" ) );
                lineItem.setMarketingPartNo( rs.getString( "P_Marketing_Part_Number" ) );
                lineItem.setManufacturingPartNo( rs.getString( "Part_Number" ) );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get Part Number Info from database", sqlex );
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return lineItem;
   }

   /**
    * Save salesOrder details
    * @param salesOrder
    * @return salesOrderId
    */

    public static int saveSalesOrder(DBTransaction dbTransaction , SalesOrder salesOrder) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int salesOrderId = -1;
        String newOdmId = null;
        String newEndCustId = null;
        try
        {
            if ( ConfigManager.isSaveEndCustomerandEndUser() )
            {
                if ( salesOrder.getOdmId() <= 0 )
                {
                     newOdmId =  getCustomerShipRelationId(dbTransaction, salesOrder.getCustomerId(), salesOrder.getNewEndCustomerName(), "End Customer" );
                }
                if ( salesOrder.getEndCustomerId() <= 0 )
                {
                    newEndCustId = getCustomerShipRelationId(dbTransaction,salesOrder.getCustomerId(), salesOrder.getNewEndUserName(), "End User" );
                }
            }
            else
            {
                newOdmId = salesOrder.getOdmId()+"";
                newEndCustId = salesOrder.getEndCustomerId()+"";
            }
            
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            //if salesOrderId == -1 then salesOrder is not exists insert the data, if exists then update it
            if(salesOrder.getSalesOrderId() == -1)
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.insertSalesOrder"));

                pstmt.setInt      (1, salesOrder.getCustomerId());
                pstmt.setString   (2, salesOrder.getCustomerPO());
                pstmt.setString   (3, salesOrder.getContactPerson());
                pstmt.setString   (4, salesOrder.getSalesPerson());
                pstmt.setString   (5, ((salesOrder.getNewShippingMethod() != null) && (salesOrder.getNewShippingMethod().length() > 0)) ? salesOrder.getNewShippingMethod().trim() : salesOrder.getShippingMethodName().trim());
                pstmt.setString   (6, salesOrder.getShippingAccount());
                pstmt.setInt      (7, salesOrder.getBillingAddressId());
                pstmt.setInt      (8, salesOrder.getShippingAddressId());
                pstmt.setString   (9, salesOrder.getComments());
                pstmt.setString   (10, salesOrder.getShippingInstructions());
                pstmt.setString   (11, salesOrder.getEndUser());
                pstmt.setString   (12, "PENDING");
                pstmt.setDate     (13, StringUtils.getSQLDate(salesOrder.getSalesOrderDate()));
                pstmt.setInt      (14, salesOrder.getIsSample() ? 0 : 1);
                pstmt.setInt      (15, salesOrder.isCancel() ? 0 : 1);
                pstmt.setInt      (16, salesOrder.isPending() ? 0 : 1);
                pstmt.setInt      (17, salesOrder.isShipped() ? 0 : 1);
                pstmt.setString   (18, salesOrder.getEnteredById());
                pstmt.setString   (19, salesOrder.getEnteredByName());
                pstmt.setString   (20, salesOrder.getSalesTaxType());

                pstmt.setDouble    (21, salesOrder.getSubTotalAsDouble());
                pstmt.setDouble    (22, salesOrder.getSalesTaxAsDouble());
                pstmt.setDouble    (23, salesOrder.getFrieghtAsDouble());
                pstmt.setDouble    (24, salesOrder.getMiscAsDouble());
                pstmt.setString   (25, salesOrder.getSalesOrderNo());
                pstmt.setString   (26, salesOrder.getRegionalRep());
                pstmt.setString   (27, salesOrder.getShippingTerms());
                pstmt.setString   (28, salesOrder.getDeliveryTerms());
                pstmt.setString   (29, salesOrder.getMiscName());
                pstmt.setString   (30, salesOrder.getBillingContactPerson());
                pstmt.setString   (31, salesOrder.getShippingContactPerson());
                pstmt.setString   (32, salesOrder.getSalesRep() );

                pstmt.setString   (33, salesOrder.getPoRev() );
                pstmt.setDate     (34, StringUtils.getSQLDate(salesOrder.getPoDate()));
                if ( salesOrder.getOdmId() <= 0 )
                {
                    pstmt.setString   (35, newOdmId);
                }
                else
                {
                    pstmt.setString   (35, salesOrder.getOdmId()+"" );
                }
                pstmt.setInt      (36, salesOrder.getIsEndCustomerShipAddress() == false ? 0 : 1 ); //IsEndCustomerShipAddress is mapping to the IsCustomerShipAddress in DB Field. if IsEndCustShipAddrs is TRUE Then isCustShipAddrs is FALSE Hence storing FALSE in the DB
                pstmt.setString   (37, salesOrder.getFreightInstructions() );
                pstmt.setString   (38, salesOrder.getShippingInstructionsId() > 0 ? salesOrder.getShippingInstructionsId()+"" : null );
                pstmt.setString   (39, salesOrder.getFreightInstructionsId() > 0 ? salesOrder.getFreightInstructionsId()+"" : null );
                if( Constants.SALESORDER_LETTER_OF_CREDIT.equalsIgnoreCase ( salesOrder.getShippingTerms() ) )
                {
                    pstmt.setString   (40, salesOrder.getLetterOfCreditNo() );
                    pstmt.setString   (41, salesOrder.getLcIssuingBank() );
                }
                else
                {
                    pstmt.setString   (40, null );
                    pstmt.setString   (41, null );
                }
                pstmt.setInt    (42, salesOrder.getQuotationId() );
                if ( salesOrder.getEndCustomerId() <= 0 )
                {
                    pstmt.setString ( 43, newEndCustId );
                }
                else
                {
                    pstmt.setString ( 43, salesOrder.getEndCustomerId()+"" );
                }
                pstmt.setString ( 44, ( salesOrder.getIsConsignmentSales() == null || "".equals ( salesOrder.getIsConsignmentSales().trim() ) ) ? "1" : salesOrder.getIsConsignmentSales() );
                pstmt.setString ( 45, salesOrder.getNewEndUserName() == null ? null : salesOrder.getNewEndUserName().trim() );
                pstmt.setString ( 46, salesOrder.getNewEndCustomerName() == null ? null : salesOrder.getNewEndCustomerName().trim() );
                pstmt.setString ( 47, salesOrder.getQuotationNo() );
                pstmt.setString ( 48, salesOrder.getUserField1() );
                pstmt.setString ( 49, salesOrder.getUserField2() );
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

			    pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.getSalesOrderId"));
                rs = pstmt.executeQuery();
                if (rs.next())
                {
                     salesOrderId = rs.getInt("SalesOrder_Id");
                }
                // (If it is new sales order insert duplicate copy into Change_Sales_Order Table)
                pstmt.close();
                pstmt = null;
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.insertChangeSalesOrder"));

                pstmt.setInt       (1, salesOrderId);
                pstmt.setInt       (2, 0);
                pstmt.setInt       (3, salesOrder.getCustomerId());
                pstmt.setString    (4, salesOrder.getCustomerPO());
                pstmt.setString    (5, salesOrder.getContactPerson());
                pstmt.setString    (6, salesOrder.getSalesPerson());
                pstmt.setString    (7, ((salesOrder.getNewShippingMethod() != null) && (salesOrder.getNewShippingMethod().length() > 0)) ? salesOrder.getNewShippingMethod().trim() : salesOrder.getShippingMethodName().trim());
                pstmt.setString    (8, salesOrder.getShippingAccount());
                pstmt.setInt       (9, salesOrder.getBillingAddressId());
                pstmt.setInt       (10, salesOrder.getShippingAddressId());
                pstmt.setString    (11, salesOrder.getComments());
                pstmt.setString    (12, salesOrder.getShippingInstructions());
                pstmt.setString    (13, salesOrder.getEndUser());
                pstmt.setString    (14, "PENDING");
                pstmt.setDate      (15, StringUtils.getSQLDate(salesOrder.getSalesOrderDate()));
                pstmt.setInt       (16, salesOrder.getIsSample() ? 0 : 1);
                pstmt.setInt       (17, salesOrder.isCancel() ? 0 : 1);
                pstmt.setInt       (18, salesOrder.isPending() ? 0 : 1);
                pstmt.setInt       (19, salesOrder.isShipped() ? 0 : 1);
                pstmt.setString    (20, salesOrder.getEnteredById());
                pstmt.setString    (21, salesOrder.getEnteredByName());
                pstmt.setString    (22, salesOrder.getSalesTaxType());

                pstmt.setDouble    (23, salesOrder.getSubTotalAsDouble());
                pstmt.setDouble    (24, salesOrder.getSalesTaxAsDouble());
                pstmt.setDouble    (25, salesOrder.getFrieghtAsDouble());
                pstmt.setDouble    (26, salesOrder.getMiscAsDouble());
                pstmt.setString    (27, salesOrder.getRegionalRep());
                pstmt.setString    (28, salesOrder.getShippingTerms());
                pstmt.setString    (29, salesOrder.getDeliveryTerms());
                pstmt.setString    (30, salesOrder.getMiscName());
                pstmt.setString    (31, salesOrder.getBillingContactPerson());
                pstmt.setString    (32, salesOrder.getShippingContactPerson());
                pstmt.setString    (33, salesOrder.getSalesRep() );

                pstmt.setString   (34, salesOrder.getPoRev() );
                pstmt.setDate     (35, StringUtils.getSQLDate(salesOrder.getPoDate()));
                pstmt.setString   (36, salesOrder.getOdmId() <= 0 ? newOdmId : salesOrder.getOdmId()+"" );
                pstmt.setString   (37, salesOrder.getFreightInstructions() );
                pstmt.setString   (38, salesOrder.getShippingInstructionsId() > 0 ? salesOrder.getShippingInstructionsId()+"" : null );
                pstmt.setString   (39, salesOrder.getFreightInstructionsId() > 0 ? salesOrder.getFreightInstructionsId()+"" : null );
                pstmt.setString   (40, salesOrder.getLetterOfCreditNo() );
                pstmt.setString   (41, salesOrder.getLcIssuingBank() );
                pstmt.setInt      (42, salesOrder.getQuotationId() );
                pstmt.setString   (43, salesOrder.getEndCustomerId() <= 0 ? newEndCustId : salesOrder.getEndCustomerId()+"" );
                pstmt.setString   ( 44, ( salesOrder.getIsConsignmentSales() == null || "".equals ( salesOrder.getIsConsignmentSales().trim() ) ) ? "1" : salesOrder.getIsConsignmentSales() );
                pstmt.setString   (45, salesOrder.getQuotationNo() );
                pstmt.executeUpdate();
            }
            else
            {
                // This Conditions never be executed
            }
            success = true;
        }
        catch (Exception se)
        {
              se.printStackTrace();
        }
        finally
        {
           if (dbTransaction == null)
           {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if( cstmt != null )
            {
                try{ cstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }
       return salesOrderId;
   }


    public static int saveChangeSalesOrder(DBTransaction dbTransaction , SalesOrder salesOrder) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int salesOrderId = -1;
        int updateStatus = 0;
        String newOdmId = null;
        String newEndCustId = null;

        try
        {

            if ( ConfigManager.isSaveEndCustomerandEndUser() )
            {
                if ( salesOrder.getOdmId() <= 0 )
                {
                     newOdmId =  getCustomerShipRelationId(dbTransaction, salesOrder.getCustomerId(), salesOrder.getNewEndCustomerName(), "End Customer" );
                }
                if ( salesOrder.getEndCustomerId() <= 0 )
                {
                    newEndCustId = getCustomerShipRelationId(dbTransaction,salesOrder.getCustomerId(), salesOrder.getNewEndUserName(), "End User" );
                }
            }
            else
            {
                newOdmId = salesOrder.getOdmId()+"";
                newEndCustId = salesOrder.getEndCustomerId()+"";
            }
            
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

           String shipMethodName = salesOrder.getShippingMethodName().trim();
           if( shipMethodName.endsWith( "#" ) )
           {
               shipMethodName = shipMethodName.substring(0, shipMethodName.indexOf("#") ).trim();
           }

            //if salesOrderId == -1 then salesOrder is not exists insert the data, if exists then update it
            if(salesOrder.getSalesOrderId() == -1)
            {
                // Never is execute this code
            }
            else
            {
                cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.updateChangeSalesOrder"));

                cstmt.setString (1, salesOrder.getCustomerPO());
                cstmt.setString (2, salesOrder.getContactPerson());
                cstmt.setString (3, salesOrder.getSalesPerson());
                cstmt.setString (4, shipMethodName);
                cstmt.setString (5, salesOrder.getShippingAccount());
                cstmt.setInt    (6, salesOrder.getShippingAddressId());
                cstmt.setString (7, salesOrder.getComments());
                cstmt.setString (8, salesOrder.getShippingInstructions());
                cstmt.setString (9, salesOrder.getEndUser());
                cstmt.setString (10, salesOrder.getSalesTaxType());
			    cstmt.setDouble (11, salesOrder.getSubTotalAsDouble());
			    cstmt.setDouble (12, salesOrder.getSalesTaxAsDouble());
			    cstmt.setDouble (13, salesOrder.getFrieghtAsDouble());
			    cstmt.setDouble (14, salesOrder.getMiscAsDouble());

                cstmt.setString (15, salesOrder.getSalesOrderNo());
                cstmt.setString (16, salesOrder.getRegionalRep());
                cstmt.setString (17, salesOrder.getShippingTerms());
                cstmt.setString (18, salesOrder.getDeliveryTerms());
                cstmt.setDate   (19, StringUtils.getSQLDate(salesOrder.getSalesOrderDate()));
                cstmt.setInt    (20, salesOrder.getSalesOrderId());
                cstmt.setString (21, salesOrder.getReason());
			    cstmt.setString (22, salesOrder.getPhoneNumber());
                cstmt.setString (23, salesOrder.getFaxNumber());
                cstmt.setString (24, salesOrder.getEmail());
			    cstmt.setString (25, salesOrder.getCellNumber());
			    cstmt.setString (26, salesOrder.getAddress());

                cstmt.registerOutParameter (27, java.sql.Types.INTEGER);
                cstmt.setString (28, salesOrder.getMiscName());
                cstmt.setInt    (29, salesOrder.getBillingAddressId () );
                cstmt.setString (30, salesOrder.getBillingContactPerson());
                cstmt.setString (31, salesOrder.getShippingContactPerson());
                cstmt.setString (32, salesOrder.getSalesRep() );

                cstmt.setString (33, salesOrder.getPoRev() );
                cstmt.setDate   (34, StringUtils.getSQLDate(salesOrder.getPoDate()));
                cstmt.setString (35, salesOrder.getOdmId() <= 0 ? newOdmId : salesOrder.getOdmId()+"" );
                cstmt.setInt    (36, salesOrder.getIsEndCustomerShipAddress() == false ? 0 : 1 ); //IsEndCustomerShipAddress is mapping to the IsCustomerShipAddress in DB Field. if IsEndCustShipAddrs is TRUE Then isCustShipAddrs is FALSE Hence storing FALSE in the DB

                cstmt.setString (37, salesOrder.getFreightInstructions() );
                cstmt.setString (38, salesOrder.getShippingInstructionsId() > 0 ? salesOrder.getShippingInstructionsId()+"" : null );
                cstmt.setString (39, salesOrder.getFreightInstructionsId() > 0 ? salesOrder.getFreightInstructionsId()+"" : null );

                if( Constants.SALESORDER_LETTER_OF_CREDIT.equalsIgnoreCase ( salesOrder.getShippingTerms() ) )
                {
                    cstmt.setString (40, salesOrder.getLetterOfCreditNo() );
                    cstmt.setString (41, salesOrder.getLcIssuingBank() );
                }
                else
                {
                    cstmt.setString (40, null );
                    cstmt.setString (41, null );
                }
                cstmt.setString (42, salesOrder.getEndCustomerId() > 0 ? salesOrder.getEndCustomerId()+"" : newEndCustId );
                cstmt.setString ( 43, salesOrder.getNewEndUserName() == null ? null : salesOrder.getNewEndUserName().trim() );
                cstmt.setString ( 44, salesOrder.getNewEndCustomerName() == null ? null : salesOrder.getNewEndCustomerName().trim() );
                cstmt.setString ( 45, salesOrder.getQuotationNo() );
                cstmt.setString ( 46, salesOrder.getUserField1() );
                cstmt.setString ( 47, salesOrder.getUserField2() );

                cstmt.execute();
                updateStatus = cstmt.getInt(27);
            }
            success = true;
        }
        catch (Exception se)
        {
            se.printStackTrace();
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if ( pstmt != null )
            {
                try{ pstmt.close(); } catch (SQLException ignore){}
            }
            if( cstmt != null )
            {
                try{ cstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try{ conn.close(); } catch (SQLException ignore){}
            }
        }
        return updateStatus;
    }

    public static String getCustomerShipRelationId( DBTransaction dbTransaction, int parentCustId, String customerName, String customerType ) throws  Exception
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset      = null;
        String id = null;
        boolean success = false;
        int custId =0;
        if ( customerName == null || customerName.trim().length() == 0 )
        {
            return id;
        }

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( "{ call SP_CREATE_NEW_CUSTOMER( ?, ? ) }" );
            pstmt.setString(1, customerName.trim());

            if ("End Customer".equalsIgnoreCase( customerType ) )
            {
                pstmt.setString(2, "End Customer");
            }
            else
            {
                pstmt.setString(2, "End User");
            }
            pstmt.executeUpdate();
            
            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

            pstmt = conn.prepareStatement( "SELECT Customer_ID FROM CUSTOMER WHERE C_Name = ?" );
            pstmt.setString( 1, customerName.trim() );
            rset = pstmt.executeQuery();
            if( rset.next() )
            {
                custId = rset.getInt( "Customer_ID" );
            }

            if ( rset != null )
            {
                rset.close();
                rset = null;
            }
            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }
            if ( custId > 0 )
            {
                if ("End Customer".equalsIgnoreCase( customerType ) )
                {
                    pstmt = conn.prepareStatement("INSERT INTO Customer_ODM_Relationship (ParentCustomer_Id, ODMCustomer_ID, RelationshipStatus_Id) " +
                            "VALUES ( ?, ? , 1) ");
                }
                else
                {
                    pstmt = conn.prepareStatement("INSERT INTO Customer_Endcustomer_Relationship (ParentCustomer_Id, EndCustomer_ID, RelationshipStatus_Id) " +
                        "VALUES ( ?, ? , 1) ");
                }
                pstmt.setInt(1, parentCustId );
                pstmt.setInt(2, custId );
                
                pstmt.executeUpdate();

                pstmt.close();
				pstmt = null;

                id = custId +"";
            }

            success = true;
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Exception while creating the new End Customer", sqlex );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success == true )
                    {
                        try
                        {
                            conn.commit ();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException ( "Exception while committing the creation of the new End Customer", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException ( "Exception while rolling the creation of the new End Customer", e );
                        }
                    }
                }
            }
            if ( rset != null )
            {
                try{ rset.close(); }catch(SQLException ignore){}
            }
            if ( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }
        }//finally
        return id;
    }

   /**
    * Save LineItems, Check for their existance and their values,
    * @param lineItems
    *
    */
    public static void insertLineItems(DBTransaction dbTransaction, int salesOrderId, ArrayList lineItems, String userId, boolean isSample) throws ScalarException
    {

        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int status = 0;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            Iterator items = lineItems.iterator();
            int isOnlyNewLineItemChange = 0;

            while(items.hasNext())
            {
                if( cstmt != null )
                {
                    cstmt.close(); cstmt = null;
                }

                LineItem lineItem = ( LineItem ) items.next();

                cstmt = conn.prepareCall( SqlQueryMappings.get ("sales.salesOrder.insertLineItems") );

                cstmt.setInt    (1, salesOrderId);
                cstmt.setInt    (2, lineItem.getLineItemNo());
                cstmt.setInt    (3, lineItem.getProductId());
                cstmt.setString (4, lineItem.getLineItemDescription() );
                cstmt.setInt    (5, lineItem.getQuantity());
                cstmt.setDouble (6, lineItem.getUnitPriceAsDouble());
                cstmt.setDouble (7, lineItem.getAmountAsDouble());

                cstmt.setDate   (8, StringUtils.getSQLDate(lineItem.getRequestDate()));
                cstmt.setString (9, userId);
                cstmt.setInt    (10, lineItem.getIsTaxable() ? 0 : 1);

                cstmt.setInt    (11, lineItem.getPoLineItemNo());
                cstmt.setInt    (12, isOnlyNewLineItemChange);
                cstmt.setInt    (13, status);
                cstmt.setInt    (14, lineItem.getIsExpedite() ? 0 : 1);
                cstmt.setInt    (15, lineItem.getQuotationLineItemId() );
                cstmt.setString (16, "-2".equals( lineItem.getCustomerPartNo() ) ? ( (lineItem.getNewCustomerPartNo() == null) ? "" : lineItem.getNewCustomerPartNo().trim() ) : lineItem.getCustomerPartNo() );
                cstmt.setInt    (17, "-2".equals( lineItem.getCustomerPartNo() ) ? 0 : 1 );
                cstmt.setDouble (18, lineItem.getCustomPriceField1());
                cstmt.setString (19, lineItem.getCustomTextField1());

                cstmt.execute();
            }
            success = true ;
        }
        catch (Exception se)
        {
            se.printStackTrace();
            throw new ScalarException ("Could not insert lineItems Info into database", se);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }



   }

   /*
    * Added an additional parameter isOnlyNewLineItemChange to the method.
    * As the sp_insert_lineItem_proc is changed and requires the  isOnlyNewLineItemChange parameter, this has been added.
    * Used to insert into Change_Sales_Order table, when only new line item is inserted and no other changes are made to the lineitem.
    */
    public static void insertLineItems( DBTransaction dbTransaction, int salesOrderId, ArrayList lineItems, String userId, int isOnlyNewLineItemChange ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int status = 0;


        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            Iterator items = lineItems.iterator();
            int lineitemsWithZeroamt =0;
            int cnt = 0;
            while( items.hasNext() )
            {

                if(cstmt != null)
                {
                    cstmt.close(); cstmt = null;
                }


                LineItem lineItem = (LineItem) items.next();
                if( items.hasNext())
                {
                    status = 0;
                }
                else
                {
                    status = 1;
                }
                cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.insertLineItems"));

                cstmt.setInt    (1, salesOrderId);
                cstmt.setInt    (2, lineItem.getLineItemNo());
                cstmt.setInt    (3, lineItem.getProductId());
                cstmt.setString (4, lineItem.getLineItemDescription() );
                cstmt.setInt    (5, lineItem.getQuantity());

                cstmt.setDouble (6, lineItem.getUnitPriceAsDouble());
                cstmt.setDouble (7, lineItem.getAmountAsDouble());
                cstmt.setDate   (8, StringUtils.getSQLDate(lineItem.getRequestDate()));
                cstmt.setString (9, userId);
                cstmt.setInt    (10, lineItem.getIsTaxable() ? 0 : 1);
                cstmt.setInt    (11, lineItem.getPoLineItemNo());
                cstmt.setInt    (12, isOnlyNewLineItemChange);
                cstmt.setInt    (13, status);
                cstmt.setInt    (14, lineItem.getIsExpedite() ? 0 : 1);
                cstmt.setInt    (15, lineItem.getQuotationLineItemId() );
                cstmt.setString (16, "-2".equals( lineItem.getCustomerPartNo() ) ? ( (lineItem.getNewCustomerPartNo() == null) ? "" : lineItem.getNewCustomerPartNo().trim() ) : lineItem.getCustomerPartNo() );
                cstmt.setInt    (17, "-2".equals( lineItem.getCustomerPartNo() ) ? 0 : 1 );
                cstmt.setDouble (18, lineItem.getCustomPriceField1());
                cstmt.setString (19, lineItem.getCustomTextField1());

                cstmt.execute();

            }
            success = true ;

        }
        catch (Exception se)
        {
            se.printStackTrace();
            throw new ScalarException ("Could not insert lineItems Info into database", se);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static void saveChangeLineItems(DBTransaction dbTransaction, int salesOrderId, ArrayList lineItems, String reason, String userId,int updateStatus, boolean isSample ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        boolean success         = false;
        User user               = null;

        try
        {

            Iterator items = lineItems.iterator();
            ArrayList itemsToInsert = new ArrayList(); //for the non existed lineITems
            ArrayList itemsToChange = new ArrayList();
            ArrayList itemsToCancel = new ArrayList();

            //Loop thru all the lineItems
            while( items.hasNext() )
            {
                LineItem lineItem = ( LineItem ) items.next();
                int lineItemId = lineItem.getLineItemId();

                if( ( lineItemId != -1 ) && ( !lineItem.isCancel() ) ) //LineItem is existed but it is not cancelled
                {
                    lineItem.setLineItemId(lineItemId);
                    itemsToChange.add(lineItem);
                }
                else if( ( lineItemId != -1 ) && ( lineItem.isCancel() ) )  //LineITem is existed but it is cancelled
                {
                    lineItem.setLineItemId(lineItemId);
                    itemsToCancel.add(lineItem);
                    itemsToChange.add(lineItem);
                }
                else if( lineItemId == -1 ) //LineItem is not existed
                {
                    itemsToInsert.add(lineItem);
                }
            }
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            int increment_status = 0;
            int lineitem_updated_status = 0;

            if(itemsToChange.size() != 0)
            {
                if( cstmt != null )
                {
                    cstmt.close();
                    cstmt = null;
                }
                int loop_status = 0;
                Iterator list = itemsToChange.iterator();
                while( list.hasNext() )
                {
                    if( cstmt != null )
                    {
                        cstmt.close();
                        cstmt = null;
                    }
				    cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.savechangeLineItems"));
                    LineItem lineItem = (LineItem) list.next();
                    if( list.hasNext() )
                    {
                        loop_status = 0;
                    }
                    else
                    {
                        loop_status = 1;
                    }
                    cstmt.setInt      (1, lineItem.getLineItemId());
                    cstmt.setInt      (2, salesOrderId);
                    cstmt.setInt      (3, lineItem.getQuantity());
                    cstmt.setDate     (4, StringUtils.getSQLDate(lineItem.getRequestDate()));
    				cstmt.setDouble   (5, lineItem.getUnitPriceAsDouble());
	    			cstmt.setDouble   (6, lineItem.getAmountAsDouble());
                    cstmt.setString   (7, userId);
                    cstmt.setString   (8, null);
                    cstmt.setString   (9,reason);
                    cstmt.setInt      (10,lineItem.getIsTaxable() ? 0 : 1);
                    cstmt.setInt      (11,lineItem.getIsCancel() ? 0 : 1 );
                    cstmt.setString   (12,lineItem.getLineItemDescription());
                    cstmt.setInt      (13,updateStatus);
                    cstmt.setInt      (14,loop_status);
                    cstmt.setInt      (15,lineItem.getPoLineItemNo());
                    cstmt.registerOutParameter (16, java.sql.Types.INTEGER);
                    cstmt.setInt(17,lineitem_updated_status);

                    cstmt.setInt(18,lineItem.getIsExpedite()? 0 : 1);
                    cstmt.setString(19, "-2".equals( lineItem.getCustomerPartNo() ) ? ( (lineItem.getNewCustomerPartNo() == null) ? "" : lineItem.getNewCustomerPartNo().trim() ) : lineItem.getCustomerPartNo() );
                    cstmt.setInt ( 20, lineItem.getProductId() );
                    cstmt.setInt    (21, "-2".equals( lineItem.getCustomerPartNo() ) ? 0 : 1 );
                    cstmt.setDouble (22, lineItem.getCustomPriceField1());
                    cstmt.setString (23, lineItem.getCustomTextField1());
                    // Ends Here.
                    cstmt.execute();

                    increment_status = cstmt.getInt(16);
                    if (increment_status == 1)
                    {
                        lineitem_updated_status = increment_status;
                    }
                }

            }
            //Run the cancel order stored procedure
            if( itemsToCancel.size() != 0 )
            {
                if( cstmt != null )
                {
                    cstmt.close();
                    cstmt = null;
                }
                cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.cancelLineItems"));
                Iterator list = itemsToCancel.iterator();
                while(list.hasNext())
                {
                    LineItem lineItem = (LineItem) list.next();
                    cstmt.setInt     (1, lineItem.getLineItemId());
                    cstmt.setString  (2, userId);
                    cstmt.setInt     (3,lineItem.getIsCancel() ? 0 : 1);
                    cstmt.addBatch();
               }
               cstmt.executeBatch();
            }

            /*
             * For passing the parameter isOnlyNewItemChange.
             * isOnlyNewItemChange is assigned a value 2 if already there is a change is the sales order apart from inserting a new line item
             * 1 is assigned if there are no other changes and only a new line item is inserted.
             */
            if ( itemsToInsert.size () != 0 )
            {
                if ( cstmt != null )
                {
                    cstmt.close ();
                    cstmt = null;
                }
                if ( conn != null )
                {
                    conn.close ();
                    conn = null;
                }
                int isOnlyNewLineItemChange = 1;
                if ( lineitem_updated_status == 1 )
                {
                    isOnlyNewLineItemChange = 2;
                }
                else
                {
                     isOnlyNewLineItemChange = 1;
                }
                insertLineItems ( dbTransaction, salesOrderId, itemsToInsert, userId, isOnlyNewLineItemChange );
            }
            success = true;

        }
        catch (Exception se)
        {
               se.printStackTrace();
               throw new ScalarException ("Could not insert lineItems Info into database", se);
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if (cstmt != null)
             {
                 try { cstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
    }

   /**
    * Check for the lineItem in the Customer_Order_Line_Item
    * @param dbTransaction
    * @return boolen
    */
    private static int isLineItemExists( DBTransaction dbTransaction, int salesOrderId, LineItem lineItem ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        int lineItemId          = -1;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ("sales.salesOrder.checkForLineItem") );
            pstmt.setInt ( 1, salesOrderId );
            pstmt.setInt ( 2, lineItem.getLineItemNo() );
            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                return ( rs.getInt("LineItem_ID") );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get LineItem Info from database", sqlex);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }

        return lineItemId;

    }

   /**
    * Check for the limits(Marketing and Finance Limits)
    *
    */
    public static void checkForLimits(DBTransaction dbTransaction,ArrayList lineItems, int salesOrderId, int customerId, String userId, int isSample) throws ScalarException
    {
        float totalOrder = 0.0f;
        float unitPrice;
        boolean marketingHold = false;
        boolean financeHold = false;
        boolean operationsHold = true;//as every order needs operations approval...

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            for( int i=0; i<lineItems.size(); i++ )
            {
                LineItem lineItem = (LineItem)lineItems.get(i);

                unitPrice = loadLineItemPrice(dbTransaction, lineItem.getProductId(),customerId, lineItem.getQuantity());

                if(unitPrice > lineItem.getUnitPrice())
                {
                    marketingHold = true;
                }
                totalOrder = totalOrder + lineItem.getAmount();
            }
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            //Update Hold Table for Operations...
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.insertHoldtable"));
            if( isSample == 1)
            {
                if(totalOrder > Integer.parseInt(SalesDAO.loadSalesOrderLimit()))
                {
                    pstmt.setInt (1,salesOrderId);
                    pstmt.setString (2, FINANCE_HOLD);
                    pstmt.setString (3, "System");
                    pstmt.setInt (4, 1);
                    pstmt.addBatch();
                }

                if (marketingHold)
                {
                    pstmt.setInt (1, salesOrderId);
                    pstmt.setString (2, MARKETING_HOLD);
                    pstmt.setString (3, "System");
                    pstmt.setInt (4, 1);
                    pstmt.addBatch();
                }
            }
            //Insert Operations Hold table
            pstmt.setInt(1, salesOrderId);
            pstmt.setString (2, OPERATIONS_HOLD);
            pstmt.setString(3, "System");
            pstmt.setInt (4, 1);
            pstmt.addBatch();

            pstmt.executeBatch();

        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not check limits from database", sqlex);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }


   public static void checkForChangeSampleOrder(DBTransaction dbTransaction, int salesOrderId, User user) throws ScalarException
   {
       Connection conn = null;
       CallableStatement cstmt = null;
       ResultSet rs = null;

       try {
            if(dbTransaction == null)
                conn = DataSource.getConnection();
            else
                conn = dbTransaction.getConnection();

            cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.checkForChangeSampleOrder"));
            cstmt.setInt(1, salesOrderId);
            cstmt.setString(2, user.getUserId());
            cstmt.setString(3, user.getGroupName());
            cstmt.execute();
       } catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not check limits from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
   }


   /**
    * Update the Alarm
    * @param reason
    * @param salesOrderId
    * @param user
    */
    public static Alarm updateAlarmForChangeOrder(DBTransaction dbTransaction, String reason, int salesOrderId, User user) throws ScalarException
    {
        Alarm oldAlarm              = null;
        int alarmId                 = 0; 

        try
        {
            alarmId = SalesDAO.getAlarmIdBySalesOrderId( dbTransaction, salesOrderId );

            if( alarmId > 0 )
            {
                //Load all the existing alarm
                //oldAlarm = AlarmDAO.loadAlarmById (alarmId);
                oldAlarm = AlarmDAO.loadSalesOrderAlarmById( dbTransaction, alarmId );
                
                oldAlarm.setLastUpdatedBy( user.getUserId() );

                Comment comment = new Comment( user.getUserId(), reason );
                oldAlarm.addComment( dbTransaction, comment );

                //this is added because when approver approvs also, it is sending with originator name
                oldAlarm.setOriginator( new AlarmUser( user.getUserId(), user.getEmail() ) );
            }
            else
            {
                throw new ScalarException ("Could not get alarmId for salesOrderId " + salesOrderId + " from database");
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get alarmId for salesOrderId " + salesOrderId + " from database");
        }
        return oldAlarm;
   }

   /**
    * Update the salesOrder table
    * @param sqlString (Table Field_Name and FieldValue)
    *
    */
    public static void updateSalesOrderFields(DBTransaction dbTransaction, String sqlString) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement (sqlString);
            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception se)
        {
              se.printStackTrace();
              throw new ScalarException ("Could not Update SalesOrder Fields from database", se);
        }
        finally
        {
           if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


   /**
    * Loads all sales orders
    *
    * @return ArrayList of sales orders IDs
    */
    public static ArrayList loadAllSalesOrderIdAndCustomerPOs() throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList salesOrderIds = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAllSalesOrderIdAndCustomerPOs"));
            rs = pstmt.executeQuery();

            String[] result = null;
            while (rs.next())
            {
                result = new String[2];
                result[0] = rs.getInt ("SalesOrder_ID")+"";
                result[1] = rs.getString ("SO_Customer_PO_Number");
                salesOrderIds.add (result);
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get SalesOrder Info from database", sqlex);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salesOrderIds;
   }


    public static ArrayList loadAllSalesOrderIdAndNumbers() throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList salesOrderIds = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAllSalesOrderIdAndNumbers"));

            rs = pstmt.executeQuery();

            String[] result = null;
            while (rs.next())
            {
                result = new String[2];
                result[0] = rs.getInt ("SalesOrder_ID")+"";
                result[1] = rs.getString ("SalesOrder_No");
                salesOrderIds.add (result);
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get SalesOrder ID and SalesOrder Number list from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salesOrderIds;
    }

    public static SalesOrder loadSalesOrderBySalesOrderNo( String salesOrderNo ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        SalesOrder salesOrder = null;

        if( salesOrderNo == null )
        {
            return null;
        }
        else{
            salesOrderNo = salesOrderNo.trim() ;
        }


        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "sales.salesOrder.loadSalesOrderBySalesOrderNo" ) );
            pstmt.setString( 1, salesOrderNo );

            rs = pstmt.executeQuery();

            if( rs.next() )
            {
                salesOrder = new SalesOrder( rs.getInt ("SalesOrder_ID"),
                                             rs.getInt ("Customer_ID"),
                                             rs.getString ("SO_Customer_PO_Number"),
                                             rs.getString ("SO_Contact_Person"),
                                             rs.getString ("SO_Sales_Person"),
                                             rs.getString ("SO_Shipping_Method"),
                                             rs.getString ("SO_Shipping_Account"),
                                             rs.getInt ("SO_Billing_Address_ID"),
                                             rs.getInt ("SO_Shipping_Address_ID"),
                                             rs.getString ("SO_Comments"),
                                             rs.getString ("SO_Shipping_Instructions"),
                                             rs.getString ("SO_EndUser"),
                                             rs.getDate("SO_Booking_Date"),
                                             ( rs.getInt( "SO_Is_Sample" ) == 0 ? true : false ));

                salesOrder.setSalesOrderNo( salesOrderNo );
                salesOrder.setEnteredById (rs.getString ("SO_Entered_BY_ID"));
                salesOrder.setEnteredByName (rs.getString ("SO_Entered_BY_Name"));
                salesOrder.setSalesTaxType (rs.getString("SO_Sales_Tax_type"));
                salesOrder.setSubTotal (rs.getFloat("SO_Sub_Total"));
                salesOrder.setSalesTax (rs.getFloat("SO_Sales_Tax"));
                salesOrder.setFrieght (rs.getFloat("SO_Frieght_Tax"));
                salesOrder.setMisc (rs.getFloat("SO_Misc"));
                salesOrder.setOrderTotal (salesOrder.getSubTotal() + salesOrder.getSalesTax() + salesOrder.getFrieght() + salesOrder.getMisc());
                salesOrder.setEnteredDate (rs.getString ("Timestamp"));
                salesOrder.setRegionalRep (rs.getString ("Regional_Rep"));
                salesOrder.setShippingTerms (rs.getString ("Shipping_Terms"));
                salesOrder.setDeliveryTerms (rs.getString ("Delivery_Terms"));
                salesOrder.setMiscName (rs.getString("SO_Misc_Name"));
                salesOrder.setBillingContactPerson(rs.getString("SO_Billing_Contact_Person"));
                salesOrder.setShippingContactPerson(rs.getString("SO_Shipping_Contact_Person"));
                salesOrder.setSalesRep( rs.getString("SO_Sales_Rep") );
               //if zero setting default selected if not user has to select manually
                salesOrder.setCustomerName(CustomerDAO.loadCustomerName(salesOrder.getCustomerId()));
                ArrayList presentList = SalesOrderDAO.loadOrderLineItems(salesOrder.getSalesOrderId());
                double total = 0.0f ; double finalSalesTax = 0.0; float taxvalue = 0.0f;
                taxvalue = SalesOrderDAO.getTaxValue(null,salesOrder.getSalesTaxType());
                Float ff = new Float(taxvalue);
				double tax = ff.doubleValue();

                for(int i=0;i<presentList.size();i++)
                {
                    LineItem templineItem = (LineItem)presentList.get(i);
                    total =  total +templineItem.getExtendedPriceAsDouble();
                    if(templineItem.getIsTaxable())
                    {
                        finalSalesTax =   finalSalesTax +( templineItem.getExtendedPriceAsDouble() * (tax/100) );
                    }
                }

                salesOrder.setSalesTaxValue(taxvalue);
                salesOrder.setFinalSubTotal(total);
                salesOrder.setFinalSalesTax(finalSalesTax);
                salesOrder.setFinalOrderTotal(total+finalSalesTax+salesOrder.getMiscAsDouble()+salesOrder.getFrieghtAsDouble());

                String clientaddressId =  UserDAO.getClientAddressId();
                String addressId = null;
                String clientname = null;

                int finalAddressId = 0;
                if(clientaddressId != null)
                {
                    StringTokenizer st = new StringTokenizer(clientaddressId,"@");
                    try
                    {
                        addressId = st.nextToken();
                        clientname = st.nextToken();
                    }catch(NoSuchElementException e)
                    {
                         clientname  = "";
                    }
                }
                if(addressId  != null)
                {
                    finalAddressId = Integer.parseInt(addressId);
                }
                Address clientAddress= UserDAO.loadClientAddressDetails( null , finalAddressId );
                salesOrder.setClientAddressAsObject(clientAddress);
                String clientAddress1 = SalesOrder.getClientAddressAsString(clientAddress);
                salesOrder.setClientAddress(clientAddress1);
                salesOrder.setClientName(clientname);
                salesOrder.setContactPersons(CustomerDAO.getAllContactPersonsForCustomer(salesOrder.getCustomerId()));
				salesOrder.setAllShippingTerms(SalesDAO.getAllShippingTerms());
                salesOrder.setAllDeliveryTerms( SalesDAO.getAllDeliveryTerms( null ) );
                //This is being added because the getHtmlView method of SalesOrder is using shippingMethodName property for displaying the shipping method
                salesOrder.setShippingMethodName(salesOrder.getShippingMethod());

                salesOrder.setCustomerName(CustomerDAO.loadCustomerName(salesOrder.getCustomerId()));
                Address address = CustomerDAO.getAddress(salesOrder.getBillingAddressId());

                StringBuffer strBufferAddress = new StringBuffer();
                if(address != null)
                {

                    if((address.getContactPerson()!=null)&&(address.getContactPerson().trim().length()!=0)){
                        strBufferAddress.append(address.getContactPerson()).append(",").append("\n");

                    }
                    if((address.getAddress1()!=null)&&(address.getAddress1().trim().length()!=0)){
                        strBufferAddress.append(address.getAddress1()).append(",").append("\n");
                    }
                    if((address.getAddress2()!=null)&&(address.getAddress2().trim().length()!=0)){
                        strBufferAddress.append(address.getAddress2()).append(",").append("\n");
                    }
					if((address.getAddress3()!=null)&&(address.getAddress3().trim().length()!=0)){
						strBufferAddress.append(address.getAddress3()).append(",").append("\n");
					}
					if((address.getAddress4()!=null)&&(address.getAddress4().trim().length()!=0)){
						strBufferAddress.append(address.getAddress4()).append(",").append("\n");
					}
                    if((address.getCity()!=null)&&(address.getCity().trim().length()!=0)){
                        strBufferAddress.append(address.getCity()).append(",");
                    }
                    if((address.getState()!=null)&&(address.getState().trim().length()!=0)){
                        strBufferAddress.append(address.getState()).append(",");
                    }
                    if((address.getZipCode()!=null)&&(address.getZipCode().trim().length()!=0)){
                        strBufferAddress.append(address.getZipCode()).append("\n");
                    }
                    if((address.getCountry()!=null)&&(address.getCountry().trim().length()!=0)){
                        strBufferAddress.append(address.getCountry());
                    }
                    salesOrder.setPhoneNumber(address.getPhoneNumber());
                    salesOrder.setFaxNumber(address.getFaxNumber());
                    salesOrder.setEmail(address.getEmail());
                    salesOrder.setCellNumber(address.getCellNumber());

                    ContactPerson billingContactPerson = null;
                    String billContactPersonName = address.getContactPerson();
                    if( billContactPersonName != null && billContactPersonName.trim().length() != 0 )
                    {
                         billingContactPerson = CustomerDAO.getAddressContactPersonDetails (salesOrder.getBillingAddressId() , billContactPersonName);
                         if(billingContactPerson == null )
                         {
                            billingContactPerson  = new ContactPerson();
                         }
                    }

                    if( billingContactPerson != null )
                    {
                        address.setPhoneNumber(billingContactPerson.getPhoneNumber());
                        address.setCellNumber(billingContactPerson.getCellNumber());
                        address.setFaxNumber(billingContactPerson.getFaxNumber());
                        address.setEmail(billingContactPerson.getEmail());
                    }
                    salesOrder.setBillingAddressAsObject(address);
					salesOrder.setBillingAddress(address.getAddressAsString());
                  }
                  address = CustomerDAO.getAddress(salesOrder.getShippingAddressId());
				  if(address != null)
                  {

                    strBufferAddress = new StringBuffer();
                    if((address.getContactPerson()!=null)&&(address.getContactPerson().trim().length()!=0)){
                        strBufferAddress.append(address.getContactPerson()).append(",").append("\n");
                    }

                    if((address.getAddress1()!=null)&&(address.getAddress1().trim().length()!=0)){
                        strBufferAddress.append(address.getAddress1()).append(",").append("\n");
                    }
                    if((address.getAddress2()!=null)&&(address.getAddress2().trim().length()!=0)){
                        strBufferAddress.append(address.getAddress2()).append(",").append("\n");
                    }
					if((address.getAddress3()!=null)&&(address.getAddress3().trim().length()!=0)){
					   strBufferAddress.append(address.getAddress3()).append(",").append("\n");
				    }
				   if((address.getAddress4()!=null)&&(address.getAddress4().trim().length()!=0)){
					   strBufferAddress.append(address.getAddress4()).append(",").append("\n");
				    }
                    if((address.getCity()!=null)&&(address.getCity().trim().length()!=0)){
                        strBufferAddress.append(address.getCity()).append(",");
                    }
                    if((address.getState()!=null)&&(address.getState().trim().length()!=0)){
                        strBufferAddress.append(address.getState()).append(",");
                    }
                    if((address.getZipCode()!=null)&&(address.getZipCode().trim().length()!=0)){
                        strBufferAddress.append(address.getZipCode()).append("\n");
                    }
                    if((address.getCountry()!=null)&&(address.getCountry().trim().length()!=0)){
                        strBufferAddress.append(address.getCountry());
                    }

                    ContactPerson shippingContactPerson = null;
                    String shippingContactPersonName = address.getContactPerson();
                    if( shippingContactPersonName != null && shippingContactPersonName.trim().length() != 0 )
                    {
                         shippingContactPerson = CustomerDAO.getAddressContactPersonDetails (salesOrder.getShippingAddressId() , shippingContactPersonName);
                         if(shippingContactPerson == null )
                         {
                            shippingContactPerson  = new ContactPerson();
                         }
                    }

                    if( shippingContactPerson != null )
                    {
                        address.setPhoneNumber(shippingContactPerson.getPhoneNumber());
                        address.setCellNumber(shippingContactPerson.getCellNumber());
                        address.setFaxNumber(shippingContactPerson.getFaxNumber());
                        address.setEmail(shippingContactPerson.getEmail());
                    }
                    salesOrder.setShippingAddressAsObject(address);
                    salesOrder.setShippingAddress(address.getAddressAsString());
				}

                ArrayList contactPersons = salesOrder.getContactPersons();
				contactPersons.add(salesOrder.getCustomerName());
				salesOrder.setContactPersons(contactPersons);

                String contactPersonName = salesOrder.getContactPerson();
				if(contactPersonName.equals(salesOrder.getCustomerName()) )
				{
					Address addressObject = CustomerDAO.getSalesOrderAddress ( salesOrder.getSalesOrderId() );
					if(addressObject != null)
					{
					    salesOrder.setContactPerson(salesOrder.getCustomerName());
						salesOrder.setEmail ( addressObject.getEmail () );
						salesOrder.setPhoneNumber ( addressObject.getPhoneNumber () );
						salesOrder.setFaxNumber ( addressObject.getFaxNumber () );
						salesOrder.setCellNumber ("");
						salesOrder.setAddress("");
				   }
				}
				else
                if(salesOrder.getEndUser().trim().equals(""))
                {
                    if(contactPersonName != null &&  contactPersonName.trim().length()!= 0)
                     {
                         ContactPerson contactPerson = null;
                         contactPerson = CustomerDAO.getContactPersonDetails (salesOrder.getCustomerId() , salesOrder.getContactPerson());
                         if(contactPerson != null)
                         {
                             salesOrder.setContactPerson( contactPerson.getContactPerson() );
                             salesOrder.setPhoneNumber( contactPerson.getPhoneNumber() );
                             salesOrder.setCellNumber( contactPerson.getCellNumber() );
                             salesOrder.setFaxNumber( contactPerson.getFaxNumber() );
                             salesOrder.setEmail( contactPerson.getEmail() );
                             salesOrder.setAddress( contactPerson.getAddress() );
                         }
                         else if(contactPerson == null)
                         {
                             contactPerson = new ContactPerson();
                             salesOrder.setContactPerson( "" );
                             salesOrder.setPhoneNumber( "" );
                             salesOrder.setCellNumber( "" );
                             salesOrder.setFaxNumber( "" );
                             salesOrder.setEmail( "" );
                             salesOrder.setAddress( "" );

                         }

                     }
                }
            }

        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get SalesOrder Info from database", sqlex);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salesOrder;

   }


   public static SalesOrder loadSalesOrder( String salesOrderId ) throws ScalarException
   {
	   return loadSalesOrder( salesOrderId, null);
   }


   /**
    * Load all the salesOrder details
    * @param salesOrderId
    * @return SalesOrder
    */
    public static SalesOrder loadSalesOrder( String salesOrderId, DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        SalesOrder salesOrder = null;
		String salesOrderNo = null;
        if(salesOrderId == null)
            return null;

        try
        {
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadSalesOrder"));
            pstmt.setInt (1, Integer.parseInt(salesOrderId));

            rs = pstmt.executeQuery();
            if(rs.next() == false) {
                //No details has been obtained for this salesOrder
            }
            else
            {
                salesOrderId = rs.getString ("SalesOrder_ID");
				salesOrderNo = rs.getString("SalesOrder_No");
                String method="";
                int customerId = 0;
                salesOrder = new SalesOrder(Integer.parseInt(salesOrderId),
                            customerId = rs.getInt ("Customer_ID"),
                            rs.getString ("SO_Customer_PO_Number"),
                            rs.getString ("SO_Contact_Person"),
                            rs.getString ("SO_Sales_Person"),
                            method = (rs.getString ("SO_Shipping_Method")).trim(),
                            (rs.getString ("SO_Shipping_Account")).trim(),
                            rs.getInt ("SO_Billing_Address_ID"),
                            rs.getInt ("SO_Shipping_Address_ID"),
                            rs.getString ("SO_Comments"),
                            rs.getString ("SO_Shipping_Instructions"),
                            rs.getString ("SO_EndUser"),
                            rs.getDate("SO_Booking_Date"),
                            (rs.getInt ("SO_Is_Sample") == 0 ? true : false));

					salesOrder.setSalesOrderNo(salesOrderNo);
                    salesOrder.setEnteredById (rs.getString ("SO_Entered_BY_ID"));
                    salesOrder.setEnteredByName (rs.getString ("SO_Entered_BY_Name"));
                    salesOrder.setSalesTaxType (rs.getString("SO_Sales_Tax_type"));

					salesOrder.setSubTotalAsDouble (rs.getDouble("SO_Sub_Total"));
					salesOrder.setSalesTaxAsDouble (rs.getDouble("SO_Sales_Tax"));
					salesOrder.setFrieghtAsDouble (rs.getDouble("SO_Frieght_Tax"));
					salesOrder.setMiscAsDouble (rs.getDouble("SO_Misc"));
					salesOrder.setOrderTotalAsDouble (salesOrder.getSubTotalAsDouble() + salesOrder.getSalesTaxAsDouble() + salesOrder.getFrieghtAsDouble() + salesOrder.getMiscAsDouble());

                    salesOrder.setEnteredDate ( StringUtils.formatDateToString ( rs.getDate ("Timestamp") ) );
                    salesOrder.setRegionalRep (rs.getString ("Regional_Rep"));
                    salesOrder.setShippingTerms (rs.getString ("Shipping_Terms"));
                    salesOrder.setDeliveryTerms (rs.getString ("Delivery_Terms"));
                    salesOrder.setMiscName (rs.getString("SO_Misc_Name"));
                    salesOrder.setBillingContactPerson(rs.getString("SO_Billing_Contact_Person"));
                    salesOrder.setShippingContactPerson(rs.getString("SO_Shipping_Contact_Person"));
                    salesOrder.setSalesRep( rs.getString("SO_Sales_Rep") );
                    salesOrder.setPoRev(rs.getString("SO_PO_Rev"));
                    salesOrder.setPoDate ( StringUtils.formatDateToString ( rs.getDate("SO_PO_Date"), null ) );
                    salesOrder.setOdmId( rs.getInt("SO_ODM_ID"));
					salesOrder.setOdm( CustomerDAO.getCustomerName( null, salesOrder.getOdmId() ) );
                    salesOrder.setIsEndCustomerShipAddress( rs.getInt("SO_Is_Customer_Ship_Address") == 0 ? false : true ); //IsCustomerShipAddress in DB Field is mapping to the IsEndCustomerShipAddress. if isCustShipAddrs is TRUE Then IsEndCustShipAddrs is FALSE Hence storing FALSE in the property

                    salesOrder.setShippingInstructionsId( rs.getInt( "SO_SHIPPING_INSTRUCTIONS_ID") );
                    salesOrder.setFreightInstructionsId( rs.getInt ( "SO_Freight_Instructions_Id" ) );
                    salesOrder.setLcIssuingBank( rs.getString ( "SO_LC_Issuing_Bank") );
                    salesOrder.setLetterOfCreditNo( rs.getString ( "SO_Letter_Of_Credit_No") );
                    salesOrder.setEndCustomerId( rs.getInt("SO_ENDCUSTOMER_ID") );
                    salesOrder.setIsConsignmentSales (rs.getString ( "SO_IS_CONSIGNMENT" ));
					salesOrder.setNewEndUserName( rs.getString ( "SO_NEW_END_USER" ) );
                    salesOrder.setNewEndCustomerName( rs.getString ( "SO_NEW_END_CUSTOMER" ) );
					salesOrder.setQuotationNo( rs.getString ( "Quotation_No") );
                    salesOrder.setUserField1(rs.getString("SO_USER_FIELD1"));
                    salesOrder.setUserField2(rs.getString("SO_USER_FIELD2"));
                    salesOrder.setFreightInstructions( rs.getString ( "Freight_Instructions") );
                    salesOrder.setEndCustomer( CustomerDAO.getCustomerName( null, salesOrder.getEndCustomerId() ) );

                    if ( salesOrder.getNewEndUserName() != null && salesOrder.getNewEndUserName().trim().length() > 0 )
                    {
                        //NEW END USER IS PRESENT SO WE NEED TO SET THE --ADD NEW-- OPTION IN THE SELECT COMBO
                        salesOrder.setEndCustomerId( -2 );
                    }
                    if ( salesOrder.getNewEndCustomerName() != null && salesOrder.getNewEndCustomerName().trim().length() > 0 )
                    {
                        //NEW END CUSTOMER IS PRESENT SO WE NEED TO SET THE --ADD NEW-- OPTION IN THE SELECT COMBO
                        salesOrder.setOdmId( -2 );
                    }
                    ArrayList presentList = SalesOrderDAO.loadOrderLineItems(salesOrder.getSalesOrderId());
                    double total = 0.0f ; double finalSalesTax = 0.0; float taxvalue = 0.0f;

                    taxvalue = SalesOrderDAO.getTaxValue(null,salesOrder.getSalesTaxType());
                    Float ff = new Float(taxvalue);
                    double tax = ff.doubleValue();

                    for(int i=0;i<presentList.size();i++)
                    {
                        LineItem templineItem = (LineItem)presentList.get(i);
                        total =  total +templineItem.getExtendedPriceAsDouble();
                        if(templineItem.getIsTaxable())
                        {
                            finalSalesTax =   finalSalesTax +( templineItem.getExtendedPriceAsDouble() * (tax/100) );
                        }
                    }
                    salesOrder.setSalesTaxValue(taxvalue);
                    salesOrder.setFinalSubTotal(total);
                    salesOrder.setFinalSalesTax(finalSalesTax);
                    salesOrder.setFinalOrderTotal(total+finalSalesTax+salesOrder.getMiscAsDouble()+salesOrder.getFrieghtAsDouble());

                    String clientaddressId =  UserDAO.getClientAddressId();
                    String addressId = null;
                    String clientname = null;

                    int finalAddressId = 0;
                    if(clientaddressId != null)
                    {
                        StringTokenizer st = new StringTokenizer(clientaddressId,"@");
                        try
                        {
                            addressId = st.nextToken();
                            clientname = st.nextToken();
                        }catch(NoSuchElementException e)
                        {
                             clientname  = "";
                        }
                    }
                    if(addressId  != null)
                    {
                        finalAddressId = Integer.parseInt(addressId);
                    }
                    Address clientAddress= UserDAO.loadClientAddressDetails( null , finalAddressId );
                    salesOrder.setClientAddressAsObject(clientAddress);
                    String clientAddress1 = SalesOrder.getClientAddressAsString(clientAddress);
                    salesOrder.setClientAddress(clientAddress1);
                    salesOrder.setClientName(clientname);
                    salesOrder.setContactPersons(CustomerDAO.getAllContactPersonsForCustomer(salesOrder.getCustomerId()));

                    salesOrder.setContactPersonsForBillingAddress(CustomerDAO.getAllContactPersonsForAddress(  salesOrder.getBillingAddressId()));  //Added by Veeru  on 24th April 2004
                    salesOrder.setContactPersonsForShippingAddress(CustomerDAO.getAllContactPersonsForAddress( salesOrder.getShippingAddressId()));  //Added by Veeru  on 24th April 2004

                    salesOrder.setAllShippingTerms(SalesDAO.getAllShippingTerms());
                    salesOrder.setAllDeliveryTerms( SalesDAO.getAllDeliveryTerms( null ) );

                    salesOrder.setShippingMethodName(salesOrder.getShippingMethod());
                    Customer customer = CustomerDAO.loadCustomerById(salesOrder.getCustomerId());
                    salesOrder.setCustomerName( customer.getCustomerName() );
                    salesOrder.setCustomerNumber( customer.getCustomerNumber() );
                    salesOrder.setIsConsignee(customer.getIsConsignee());
                    Address address = CustomerDAO.getAddressWithContactPersonDetails(salesOrder.getBillingAddressId(), salesOrder.getBillingContactPerson(), salesOrder.getCustomerId() );

                    StringBuffer strBufferAddress = new StringBuffer();
                    if(address != null)
                    {
                        salesOrder.setPhoneNumber("");
                        salesOrder.setFaxNumber("");
                        salesOrder.setEmail("");
                        salesOrder.setCellNumber("");

                        salesOrder.setBillingAddressAsObject(address);
                        salesOrder.setBillingAddress(address.getAddressAsString());
                  }
                  int shippingAddressId = salesOrder.getShippingAddressId();

                  if ( shippingAddressId < 0 )
                  {
                      shippingAddressId = getDefaultShippingAddressId( salesOrder.getCustomerId () );
                      salesOrder.setShippingAddressId( shippingAddressId );
                      salesOrder.setShippingContactPerson(CustomerDAO.getAddress( shippingAddressId ).getContactPerson());
                  }
                  address = CustomerDAO.getAddressWithContactPersonDetails( shippingAddressId, salesOrder.getShippingContactPerson() );

                  String shipAddressWithoutContactPerson =  getAddressWithoutContactPersonAsString ( address );
                  if ( "".equals( shipAddressWithoutContactPerson.trim() ) )
                  {
                      int defaultShippingAddressId = getDefaultShippingAddressId( salesOrder.getCustomerId () );
                      if ( defaultShippingAddressId != shippingAddressId )
                      {
                            salesOrder.setShippingAddressId( defaultShippingAddressId );
                            salesOrder.setShippingContactPerson(CustomerDAO.getAddress( defaultShippingAddressId ).getContactPerson());
                            address = CustomerDAO.getAddressWithContactPersonDetails( defaultShippingAddressId, salesOrder.getShippingContactPerson() );
                      }
                  }

				  if(address != null)
                  {
                    salesOrder.setShippingAddressAsObject(address);
                    salesOrder.setShippingAddress(address.getAddressAsString());
                  }

				ArrayList contactPersons = salesOrder.getContactPersons();
				contactPersons.add(salesOrder.getCustomerName());
				salesOrder.setContactPersons(contactPersons);

				String contactPersonName = salesOrder.getContactPerson();

				if(contactPersonName.equals(salesOrder.getCustomerName()) )
				{
					Address addressObject = CustomerDAO.getSalesOrderAddress ( salesOrder.getSalesOrderId() );
					if(addressObject != null)
					{
					    salesOrder.setContactPerson(salesOrder.getCustomerName());
						salesOrder.setEmail ( addressObject.getEmail () );
						salesOrder.setPhoneNumber ( addressObject.getPhoneNumber () );
						salesOrder.setFaxNumber ( addressObject.getFaxNumber () );
						salesOrder.setCellNumber ("");
						salesOrder.setAddress("");
				   }
				}
				else
                // Modified by Prabhanjan (16 Apr 2004) - Added if conditon.
                //if(salesOrder.getEndUser().trim().equals(""))
                //{
                    if(contactPersonName != null &&  contactPersonName.trim().length()!= 0)
                     {
                         ContactPerson contactPerson = null;
                         contactPerson = CustomerDAO.getContactPersonDetails (salesOrder.getCustomerId() , salesOrder.getContactPerson());
                         if(contactPerson != null)
                         {
                             salesOrder.setContactPerson( contactPerson.getContactPerson() );
                             salesOrder.setPhoneNumber( contactPerson.getPhoneNumber() );
                             salesOrder.setCellNumber( contactPerson.getCellNumber() );
                             salesOrder.setFaxNumber( contactPerson.getFaxNumber() );
                             salesOrder.setEmail( contactPerson.getEmail() );
                             salesOrder.setAddress( contactPerson.getAddress() );
                         }
                         else if(contactPerson == null)
                         {
                             contactPerson = new ContactPerson();
                             salesOrder.setContactPerson( "" );
                             salesOrder.setPhoneNumber( "" );
                             salesOrder.setCellNumber( "" );
                             salesOrder.setFaxNumber( "" );
                             salesOrder.setEmail( "" );
                             salesOrder.setAddress( "" );

                         }

                     }
                //}

                //salesOrder.setQuotationNo( QuotationDAO.loadQuotationNo( null, salesOrder.getQuotationId() ) );
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get SalesOrder Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return salesOrder;

   }



   /**
	* Load all the salesOrder details
	* @param alarmId
	* @return SalesOrder
	*/
   public static SalesOrder loadSalesOrderByAlarmId(String alarmId) throws ScalarException
   {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		SalesOrder salesOrder = null;
		String salesOrderNo = null;
		String salesOrderId = null;

		if(alarmId == null)
			return null;

		try {
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadSalesOrderByAlarmId"));
			pstmt.setInt (1, Integer.parseInt(alarmId));
            

            rs = pstmt.executeQuery();
			if(rs.next()){
                int customerId = 0;
				salesOrderId = rs.getString ("SalesOrder_ID");
				salesOrderNo = rs.getString("SalesOrder_No");
				salesOrder = new SalesOrder(Integer.parseInt(salesOrderId),
							customerId = rs.getInt ("Customer_ID"),
							rs.getString ("SO_Customer_PO_Number"),
							rs.getString ("SO_Contact_Person"),
							rs.getString ("SO_Sales_Person"),
							rs.getString ("SO_Shipping_Method"),
							rs.getString ("SO_Shipping_Account"),
							rs.getInt ("SO_Billing_Address_ID"),
							rs.getInt ("SO_Shipping_Address_ID"),
							rs.getString ("SO_Comments"),
							rs.getString ("SO_Shipping_Instructions"),
							rs.getString ("SO_EndUser"),
							rs.getDate("SO_Booking_Date"),
							(rs.getInt ("SO_Is_Sample") == 0 ? true : false));

					salesOrder.setSalesOrderNo(salesOrderNo);
					salesOrder.setEnteredById (rs.getString ("SO_Entered_BY_ID"));
					salesOrder.setEnteredByName (rs.getString ("SO_Entered_BY_Name"));
					salesOrder.setSalesTaxType (rs.getString("SO_Sales_Tax_type"));

                    salesOrder.setSubTotalAsDouble (rs.getDouble("SO_Sub_Total"));
					salesOrder.setSalesTaxAsDouble (rs.getDouble("SO_Sales_Tax"));
					salesOrder.setFrieghtAsDouble (rs.getDouble("SO_Frieght_Tax"));
					salesOrder.setMiscAsDouble (rs.getDouble("SO_Misc"));
					salesOrder.setOrderTotalAsDouble (salesOrder.getSubTotalAsDouble() + salesOrder.getSalesTaxAsDouble() + salesOrder.getFrieghtAsDouble() + salesOrder.getMiscAsDouble());

                    salesOrder.setEnteredDate (rs.getString ("Timestamp"));
					salesOrder.setRegionalRep (rs.getString ("Regional_Rep"));
					salesOrder.setShippingTerms (rs.getString ("Shipping_Terms"));
					salesOrder.setDeliveryTerms (rs.getString ("Delivery_Terms"));
					salesOrder.setMiscName (rs.getString("SO_Misc_Name"));
                    salesOrder.setBillingContactPerson(rs.getString("SO_Billing_Contact_Person"));
                    salesOrder.setShippingContactPerson(rs.getString("SO_Shipping_Contact_Person"));

                    salesOrder.setSalesRep( rs.getString("SO_Sales_Rep") );
                    salesOrder.setPoRev(rs.getString("SO_PO_Rev"));
                    salesOrder.setPoDate ( StringUtils.formatDateToString ( rs.getDate("SO_PO_Date"), null ) );
                    salesOrder.setOdmId( rs.getInt("SO_ODM_ID"));
                    salesOrder.setFreightInstructions( rs.getString("SO_Freight_Instructions") );
                    salesOrder.setQuotationId( rs.getInt ( "Quotation_Id") );
                    salesOrder.setEndCustomerId( rs.getInt("SO_ENDCUSTOMER_ID") );
                    salesOrder.setQuotationNo( rs.getString ( "Quotation_No") );
                    
                    salesOrder.setOdm( CustomerDAO.getCustomerName( null, salesOrder.getOdmId() ) );
                    salesOrder.setEndCustomer( CustomerDAO.getCustomerName( null, salesOrder.getEndCustomerId() ) );
                    //salesOrder.setQuotationNo( QuotationDAO.loadQuotationNo( null, salesOrder.getQuotationId() ) );


                  salesOrder.setCustomerName(CustomerDAO.loadCustomerName(salesOrder.getCustomerId()));
				  //Address address = CustomerDAO.getAddress(salesOrder.getBillingAddressId());
                  Address address = CustomerDAO.getAddressWithContactPersonDetails(salesOrder.getBillingAddressId(), salesOrder.getBillingContactPerson(), salesOrder.getCustomerId() );
				  if(address != null) 
                  {

                    salesOrder.setPhoneNumber("");
                    salesOrder.setFaxNumber("");
                    salesOrder.setEmail("");
                    salesOrder.setCellNumber("");

                    salesOrder.setBillingAddressAsObject(address);
                    salesOrder.setBillingAddress(address.getAddressAsString());
				  }
                  int shippingAddressId = salesOrder.getShippingAddressId();
                  if ( shippingAddressId < 0 )
                  {
                      shippingAddressId = getDefaultShippingAddressId( salesOrder.getCustomerId () );
                      salesOrder.setShippingAddressId( shippingAddressId );
                      salesOrder.setShippingContactPerson(CustomerDAO.getAddress( shippingAddressId ).getContactPerson());
                  }
                  //address = CustomerDAO.getAddress( shippingAddressId );
                  address = CustomerDAO.getAddressWithContactPersonDetails( shippingAddressId, salesOrder.getShippingContactPerson() );
                  String shipAddressWithoutContactPerson = getAddressWithoutContactPersonAsString ( address );
                  if ( "".equals( shipAddressWithoutContactPerson.trim() ) )
                  {
                      int defaultShippingAddressId = getDefaultShippingAddressId( salesOrder.getCustomerId () );
                      if ( defaultShippingAddressId != shippingAddressId )
                      {
                            salesOrder.setShippingAddressId( defaultShippingAddressId );
                            salesOrder.setShippingContactPerson(CustomerDAO.getAddress( defaultShippingAddressId ).getContactPerson());
                            //address = CustomerDAO.getAddress( defaultShippingAddressId );
                            address = CustomerDAO.getAddressWithContactPersonDetails( defaultShippingAddressId, salesOrder.getShippingContactPerson() );
                      }
                  }
				  if(address != null)
                  {
                    salesOrder.setShippingAddressAsObject(address);
                    salesOrder.setShippingAddress(address.getAddressAsString());
                  }
                    String contactPersonName = salesOrder.getContactPerson();
                    if(contactPersonName.equals(salesOrder.getCustomerName()) )
                    {
                        Address addressObject = CustomerDAO.getSalesOrderAddress ( salesOrder.getSalesOrderId() );
                        if(addressObject != null)
                        {
                            salesOrder.setContactPerson(salesOrder.getCustomerName());
                            salesOrder.setEmail ( addressObject.getEmail () );
                            salesOrder.setPhoneNumber ( addressObject.getPhoneNumber () );
                            salesOrder.setFaxNumber ( addressObject.getFaxNumber () );
                            salesOrder.setCellNumber ("");
                            salesOrder.setAddress("");
                       }
                    }
                    else if(contactPersonName != null &&  contactPersonName.trim().length()!= 0) {
                         ContactPerson contactPerson = null;
                         contactPerson = CustomerDAO.getContactPersonDetails (salesOrder.getCustomerId() , salesOrder.getContactPerson());
                         if(contactPerson != null)
                         {
                             salesOrder.setContactPerson( contactPerson.getContactPerson() );
                             salesOrder.setPhoneNumber( contactPerson.getPhoneNumber() );
                             salesOrder.setCellNumber( contactPerson.getCellNumber() );
                             salesOrder.setFaxNumber( contactPerson.getFaxNumber() );
                             salesOrder.setEmail( contactPerson.getEmail() );
                             salesOrder.setAddress( contactPerson.getAddress() );
                         }
                         else if(contactPerson == null)
                         {
                             contactPerson = new ContactPerson();
                             salesOrder.setContactPerson( "" );
                             salesOrder.setPhoneNumber( "" );
                             salesOrder.setCellNumber( "" );
                             salesOrder.setFaxNumber( "" );
                             salesOrder.setEmail( "" );
                             salesOrder.setAddress( "" );
                         }
                     }
			}
		}
		catch (Exception sqlex) {
			sqlex.printStackTrace();
			throw new ScalarException ("Could not get SalesOrder Info from database for this "+alarmId, sqlex);
		}
		finally {
			if (rs != null) {
				try { rs.close(); } catch (SQLException excp) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException excp) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException excp) {}
			}
		}

		return salesOrder;

   }




   /**
    * Load all the change order line items to show
    * @param salesOrderId
    * @return LineItems (ArrayList)
    */
   public static ArrayList loadChangeOrderLineItems(int salesOrderId) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList lineItems = new ArrayList();

        if(salesOrderId == -1)
            return null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.loadAllPreviousOrderLineItems"));
            pstmt.setInt(1,salesOrderId);
            rs = pstmt.executeQuery();
            LineItem lineItem = null;

            //Loop through all the change order lineitems
            while(rs.next())
            {
                lineItem = new LineItem();
                lineItem.setLineItemNo(rs.getInt("COMMON_Line_Item_No"));

                lineItem.setProductId(rs.getInt("COMMON_Product_ID"));


                lineItem.setQuantity(rs.getInt("COMMON_Qty"));

				lineItem.setUnitPriceAsDouble(rs.getDouble("COMMON_Unit_Cost"));
				lineItem.setAmountAsDouble(rs.getDouble("COMMON_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COMMON_Requested_Date"),null));
                lineItem.setStatus(rs.getString("COMMON_Status"));
                lineItem.setChangeNumber(rs.getInt("COMMON_Change_Number"));

                lineItem.setLineItemId( rs.getInt( "COMMON_Line_Item_Id" ) );
                lineItem.setCustomerPartNo(rs.getString("Customer_PartNumber"));
                lineItem.setLabelNumber(rs.getString("Label_Number") );
                lineItem.setManufacturingPartNo(rs.getString("PART_NUMBER"));
                lineItem.setMarketingPartNo(rs.getString("MKTG_PART_NUMBER"));
                lineItem.setDescription(rs.getString("COOL_DESCRIPTION"));
                //loadMarketingAndMfgPartNumbers(lineItem);

                lineItems.add(lineItem);
            }

        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get change order lineItems Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }


        return lineItems;
   }

   /**
    * Load the Change Number
    * @param salesOrderId
    * @return changeNo
    */
   public static int loadChangeNo(int salesOrderId) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int changeNo = 1;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadChangeNo"));
            pstmt.setInt (1, salesOrderId);

            rs = pstmt.executeQuery();

            while(rs.next())
            {
                changeNo = rs.getInt(1);
            }
            success = true;
        }

        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Change Number for this orderId", sqlex);
        }
        finally
        {
            try
            {
                if (conn != null) {
                    if (success == true)
                        conn.commit();
                    else
                        conn.rollback();
                }
            }
            catch (SQLException ignore) {}
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return changeNo;
   }

   /**
    * Load the Max LineItemNO
    * @param salesOrderId
    * @return lineItemNO
    */
   public static int loadMaxLineItemNo(int salesOrderId) throws ScalarException
   {
        return loadMaxLineItemNo( null, salesOrderId );
   }

    /**
    * Load the Max LineItemNO
    * @param salesOrderId
    * @return lineItemNO
    */
   public static int loadMaxLineItemNo( DBTransaction dbTransaction, int salesOrderId) throws ScalarException
   {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        int lineItemNo = 0;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.loadMaxLineItemNo"));
            cstmt.setInt(1,salesOrderId);
            cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
            cstmt.execute();

            lineItemNo = cstmt.getInt(2);
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get max lineItem Number for this orderId", sqlex);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return lineItemNo+1;
   }

   /**
    * This method is used to return number of line items for selected sales order id
    * author : Vasu
    * @param salesOrderId
    * @return int )
    */
   public static int openSalesOrderLineItemCount (int salesOrderId) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int lineItemCount = -1;
        try {
            conn = DataSource.getConnection();
            //count all the open order lineItems
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.openSalesOrderLineItemsCount"));
            pstmt.setInt (1, salesOrderId);
            rs = pstmt.executeQuery();

            while(rs.next())
            {
              lineItemCount = rs.getInt("countItems");
            }

        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get present lineItems Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return lineItemCount;
   }

   public static ArrayList loadOrderLineItems(int salesOrderId) throws ScalarException
   {
       return loadOrderLineItems(null,salesOrderId);
   }

   public static ArrayList loadPendingOrderLineItems(int salesOrderId,int customerId) throws ScalarException
   {
       return loadPendingOrderLineItems(null,salesOrderId,customerId);
   }


    public static ArrayList loadPendingOrderLineItems(DBTransaction dbTransaction,int salesOrderId,int customerId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList lineItems = new ArrayList();
        int odmId = -1;
        if(salesOrderId == -1)
            return null;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                    conn = DataSource.getConnection();
            }
            else {
                    conn = dbTransaction.getConnection();
            }

            /*pstmt = conn.prepareStatement("SELECT SO_Odm_id from Sales_Order where salesOrder_Id = ?");
            pstmt.setInt (1, salesOrderId);
            rs = pstmt.executeQuery();
            if(rs.next() ){
                odmId = rs.getInt("SO_Odm_id");
            }
            rs.close() ;
            pstmt.close();

            //Load all the open order lineItems
            if( odmId <= 0 )
            {
                pstmt = conn.prepareStatement ("SELECT Customer_PartNumber,Label_Number,Customer_Open_Order_LineItem.*, Sales_Order.SalesOrder_No FROM Customer_Open_Order_LineItem left outer join Customer_PartNumbers on  Customer_Open_Order_LineItem.product_Id =Customer_PartNumbers.product_Id and Customer_PartNumbers.customer_id = ? and Customer_PartNumbers.odm_id is Null, Sales_Order WHERE Sales_Order.SalesOrder_ID = ? and sales_order.salesOrder_id = Customer_Open_Order_LineItem.salesOrder_id and COOL_Status <> 'APPROVED' and COOL_Status <> 'SHIPPED'");
                pstmt.setInt (1, customerId);
	            pstmt.setInt (2, salesOrderId);
            }
            else{
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadPendingOrderLineItems"));
                pstmt.setInt (1, customerId);
                pstmt.setInt (2, odmId );
                pstmt.setInt (3, salesOrderId );

            }*/
            pstmt = conn.prepareStatement ("SELECT Customer_Open_Order_LineItem.*, Sales_Order.SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE Sales_Order.SalesOrder_ID = ? and sales_order.salesOrder_id = Customer_Open_Order_LineItem.salesOrder_id and COOL_Status <> 'APPROVED' and COOL_Status <> 'SHIPPED'");
            pstmt.setInt (1, salesOrderId);

            rs = pstmt.executeQuery();
            LineItem lineItem = null;

            while(rs.next()) {
                lineItem = new LineItem();

                lineItem.setSalesOrderId (salesOrderId);

                lineItem.setLineItemId(rs.getInt("LineItem_ID"));
                lineItem.setLineItemNo(rs.getInt("COOL_Line_Item_No"));
                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("COOL_Qty"));
				lineItem.setUnitPriceAsDouble(rs.getDouble("COOL_Unit_Cost"));
				lineItem.setAmountAsDouble(rs.getDouble("COOL_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COOL_Requested_Date"),null));
                lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_With_Substitutables"),null));
                lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_Without_Substitutables"),null));
				lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("COOL_Schedule_Date"),null));
                lineItem.setRevisedDate(StringUtils.formatDateToString(rs.getDate("COOL_Revised_Date"),null));
                lineItem.setStatus(rs.getString("COOL_Status"));
                lineItem.setIsTaxable((rs.getInt("COOL_Taxable") == 0) ? true : false);
                lineItem.setSalesOrderNo (rs.getString ("SalesOrder_No"));

                lineItem.setIsCancel((rs.getInt("COOL_Is_Cancel") == 0) ? true : false);
                String suggDate = lineItem.getSuggScheduleDateWithoutSubstitutables();
                if ( ( suggDate != null ) && ( suggDate.trim().length() > 0 ) ) {
                    lineItem.setSuggScheduleDate (suggDate);
                }
                lineItem.setCustomerPartNo(rs.getString("COOL_Customer_PartNumber"));
                //lineItem.setLabelNumber(rs.getString("Label_Number") ); //Added by varma 22 Nov 2004
                String scheduleDate = lineItem.getScheduleDate();

                loadMarketingAndMfgPartNumbers(lineItem);

                loadHoldsForLineItem(lineItem);

                lineItems.add(lineItem);
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get present lineItems Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return lineItems;
   }


   /**
    * Load all the order line items to show
    * @param salesOrderId
    * @return LineItems (ArrayList)
    */
   public static ArrayList loadOrderLineItems(DBTransaction dbTransaction,int salesOrderId) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList lineItems = new ArrayList();
        String coolStatus = null;
        boolean success = false;
        int isCancel;

        int lineItemNo;
        int poLineItemNo;

        if(salesOrderId == -1)
            return null;

        try {

            if (dbTransaction == null) {
                    conn = DataSource.getConnection();
            }
            else {
                    conn = dbTransaction.getConnection();
            }
            //Load all the open order lineItems
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadOrderLineItems"));
            pstmt.setInt (1, salesOrderId);
            rs = pstmt.executeQuery();
            LineItem lineItem = null;

            while(rs.next()) {
                lineItem = new LineItem();

                lineItem.setSalesOrderId (salesOrderId);
                lineItem.setLineItemId(rs.getInt("LineItem_ID"));

                lineItem.setLineItemNo(rs.getInt("COOL_Line_Item_No"));
                lineItem.setPoLineItemNo(rs.getInt("COOL_PO_Line_Item_No"));

                lineItem.setPoLineItemNo(rs.getInt("COOL_PO_Line_Item_No"));

                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("COOL_Qty"));
				lineItem.setUnitPriceAsDouble(rs.getDouble("COOL_Unit_Cost"));
				lineItem.setAmountAsDouble(rs.getDouble("COOL_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COOL_Requested_Date"),null));
                lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_With_Substitutables"),null));
                lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_Without_Substitutables"),null));
                lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("COOL_Schedule_Date"),null));
                lineItem.setRevisedDate(StringUtils.formatDateToString(rs.getDate("COOL_Revised_Date"),null));

                lineItem.setIsCancel( (rs.getInt( "COOL_Is_Cancel" ) == 0 ) ? true : false );
                isCancel = rs.getInt( "COOL_Is_Cancel" );
                if( isCancel == 0 )
                {
                    coolStatus = COOL_STATUS;
                }
                else
                {
                    coolStatus = rs.getString( "COOL_Status" );
                }
                lineItem.setStatus( coolStatus );
                lineItem.setOldStatus(rs.getString( "COOL_Status" ));
                lineItem.setIsTaxable((rs.getInt("COOL_Taxable") == 0) ? true : false);
                lineItem.setSalesOrderNo (rs.getString ("SalesOrder_No"));

                String suggDate = lineItem.getSuggScheduleDateWithoutSubstitutables();
                lineItem.setSuggScheduleDate (suggDate);

                lineItem.setLineItemDescription(rs.getString("COOL_Description"));
                lineItem.setIsExpedite( ( rs.getInt("COOL_Is_Expedite") == 0 ) ? true : false );
                lineItem.setCustomerPartNo( rs.getString("COOL_CUSTOMER_PARTNUMBER"));
                lineItem.setCustomPriceField1(rs.getDouble("COOL_CUSTOM_PRICE_FIELD1"));
                lineItem.setCustomTextField1(rs.getString("COOL_CUSTOM_TEXT_FIELD1"));
                String scheduleDate = lineItem.getScheduleDate();
                String revisedDate = lineItem.getRevisedDate();

                loadMarketingAndMfgPartNumbers(lineItem);

                loadHoldsForLineItem(lineItem);

                lineItems.add(lineItem);
            }

            success = true;
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get present lineItems Info from database", sqlex);
        }
        finally {


            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }

            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }

        }

        return lineItems;
   }

    public static ArrayList loadOrderLineItemsByCustomerId(int salesOrderId,int customerId) throws ScalarException
   {
           return  loadOrderLineItemsByCustomerId(null,salesOrderId, customerId);
    }

   public static ArrayList loadOrderLineItemsByCustomerId(DBTransaction dbTransaction,int salesOrderId,int customerId) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList lineItems = new ArrayList();
        String coolStatus = null;
        boolean success = false;
        int isCancel;
        int odmId = -1;
        int lineItemNo;
        int poLineItemNo;

        if(salesOrderId == -1)
            return null;

        try {
            if (dbTransaction == null) {
                    conn = DataSource.getConnection();
            }
            else {
                    conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT SO_Odm_id from Sales_Order where salesOrder_Id = ?");
            pstmt.setInt (1, salesOrderId);
            rs = pstmt.executeQuery();
            if(rs.next() ){
                odmId = rs.getInt("SO_Odm_id");
            }
            rs.close() ;
            pstmt.close();
            //Load all the open order lineItems
            /*if( odmId <= 0 )
            {
                pstmt = conn.prepareStatement ("SELECT Customer_Open_Order_LineItem.* ,Sales_Order.SalesOrder_No ,Customer_Partnumber,Label_Number FROM  Customer_Open_Order_LineItem left outer join Customer_Partnumbers on  Customer_Open_Order_LineItem.product_Id =Customer_PartNumbers.product_Id and Customer_PartNumbers.customer_id = ? and Customer_PartNumbers.odm_id is Null,Sales_Order WHERE   Customer_Open_Order_LineItem.salesOrder_id = Sales_Order.salesOrder_id and Sales_Order.salesOrder_id = ?");
                pstmt.setInt (1, customerId);
	            pstmt.setInt (2, salesOrderId);
            }
            else{
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadOrderLineItemsByCustomerId"));
                pstmt.setInt (1, customerId);
                pstmt.setInt (2, odmId );
                pstmt.setInt (3, salesOrderId );

            }*/
            pstmt = conn.prepareStatement ("SELECT Customer_Open_Order_LineItem.* ,Sales_Order.SalesOrder_No FROM  Customer_Open_Order_LineItem, Sales_Order WHERE   Customer_Open_Order_LineItem.salesOrder_id = Sales_Order.salesOrder_id and Sales_Order.salesOrder_id = ?");
            pstmt.setInt (1, salesOrderId);


            rs = pstmt.executeQuery();
            LineItem lineItem = null;

            while(rs.next()) {
                lineItem = new LineItem();

                lineItem.setSalesOrderId (salesOrderId);
                lineItem.setLineItemId(rs.getInt("LineItem_ID"));
                lineItem.setLineItemNo(rs.getInt("COOL_Line_Item_No"));
                lineItem.setPoLineItemNo(rs.getInt("COOL_PO_Line_Item_No"));

                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("COOL_Qty"));
				lineItem.setUnitPriceAsDouble(rs.getDouble("COOL_Unit_Cost"));
				lineItem.setAmountAsDouble(rs.getDouble("COOL_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COOL_Requested_Date"),null));
                lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_With_Substitutables"),null));
                lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_Without_Substitutables"),null));
                lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("COOL_Schedule_Date"),null));
                lineItem.setRevisedDate(StringUtils.formatDateToString(rs.getDate("COOL_Revised_Date"),null));
                lineItem.setShipmentNo( rs.getInt("COOL_Shipment_No") );

                lineItem.setIsCancel( (rs.getInt( "COOL_Is_Cancel" ) == 0 ) ? true : false );
                isCancel = rs.getInt( "COOL_Is_Cancel" );
                if( isCancel == 0 )
                {
                    coolStatus = COOL_STATUS;
                }
                else
                {
                    coolStatus = rs.getString( "COOL_Status" );
                }
                lineItem.setStatus( coolStatus );
                lineItem.setOldStatus(rs.getString( "COOL_Status" ));
                lineItem.setIsTaxable((rs.getInt("COOL_Taxable") == 0) ? true : false);
                lineItem.setSalesOrderNo (rs.getString ("SalesOrder_No"));

                String suggDate = lineItem.getSuggScheduleDateWithoutSubstitutables();
                lineItem.setSuggScheduleDate (suggDate);

                lineItem.setLineItemDescription(rs.getString("COOL_Description"));
                lineItem.setCustomerPartNo(rs.getString ("COOL_Customer_Partnumber"));
                //lineItem.setLabelNumber(rs.getString("Label_Number") );
                lineItem.setIsExpedite ( ( rs.getInt ( "COOL_Is_Expedite" ) == 0 ) ? true : false );
                lineItem.setCustomPriceField1(rs.getDouble("COOL_CUSTOM_PRICE_FIELD1"));
                lineItem.setCustomTextField1(rs.getString("COOL_CUSTOM_TEXT_FIELD1"));

                String scheduleDate = lineItem.getScheduleDate();
                String revisedDate = lineItem.getRevisedDate();

                loadMarketingAndMfgPartNumbers(lineItem);

                loadHoldsForLineItem(lineItem);

                lineItems.add(lineItem);
            }
            success = true;
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get present lineItems Info from database", sqlex);
        }
        finally
        {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }

            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }

        }
        return lineItems;
   }

    public static ArrayList loadOrderLineItemsForSplitting ( DBTransaction dbTransaction, int salesOrderId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList lineItems = new ArrayList ();
        String coolStatus = null;
        boolean success = false;
        int isCancel;

        int lineItemNo;
        int poLineItemNo;

        if ( salesOrderId == -1 )
            return null;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }


            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.salesOrder.loadOrderLineItemsForSplitting" ) );
            pstmt.setInt ( 1, salesOrderId );

            rs = pstmt.executeQuery ();
            LineItem lineItem = null;

            while ( rs.next () )
            {
                lineItem = new LineItem ();

                lineItem.setSalesOrderId ( salesOrderId );
                lineItem.setSalesOrderNo ( rs.getString ( "SalesOrder_No" ) );
                lineItem.setLineItemId ( rs.getInt ( "LineItem_ID" ) );
                lineItem.setLineItemNo ( rs.getInt ( "COOL_Line_Item_No" ) );
                lineItem.setPoLineItemNo ( rs.getInt ( "COOL_PO_Line_Item_No" ) );
                lineItem.setProductId ( rs.getInt ( "Product_ID" ) );
                lineItem.setQuantity ( rs.getInt ( "COOL_Qty" ) );
				lineItem.setUnitPriceAsDouble ( rs.getDouble ( "COOL_Unit_Cost" ) );
				lineItem.setAmountAsDouble ( rs.getDouble ( "COOL_Amount" ) );
                lineItem.setRequestDate ( StringUtils.formatDateToString ( rs.getDate ( "COOL_Requested_Date" ), null ) );
                lineItem.setSuggScheduleDateWithSubstitutables ( StringUtils.formatDateToString ( rs.getDate ( "COOL_Sugg_Schedule_Date_With_Substitutables" ), null ) );
                lineItem.setSuggScheduleDateWithoutSubstitutables ( StringUtils.formatDateToString ( rs.getDate ( "COOL_Sugg_Schedule_Date_Without_Substitutables" ), null ) );
                lineItem.setScheduleDate ( StringUtils.formatDateToString ( rs.getDate ( "COOL_Schedule_Date" ), null ) );
                lineItem.setRevisedDate ( StringUtils.formatDateToString ( rs.getDate ( "COOL_Revised_Date" ), null ) );

                isCancel = rs.getInt ( "COOL_Is_Cancel" );
                lineItem.setIsCancel ( ( isCancel == 0 ) ? true : false );

                if ( isCancel == 0 )
                {
                    coolStatus = COOL_STATUS;
                }
                else
                {
                    coolStatus = rs.getString ( "COOL_Status" );
                }
                lineItem.setStatus ( coolStatus );
                lineItem.setOldStatus ( rs.getString ( "COOL_Status" ) );
                lineItem.setIsTaxable ( ( rs.getInt ( "COOL_Taxable" ) == 0 ) ? true : false );

                String suggDate = lineItem.getSuggScheduleDateWithoutSubstitutables ();
                lineItem.setSuggScheduleDate ( suggDate );

                lineItem.setLineItemDescription ( rs.getString ( "COOL_Description" ) );
                lineItem.setShippedQty ( rs.getInt ( "Total_Shipped_Issued_Qty" ) );
                lineItem.setCustomerPartNo( rs.getString ("Customer_PartNumber"));
                lineItem.setFinalStatus( rs.getString ( "COOL_Final_Status" ) );
                lineItem.setIsExpedite( ( rs.getInt("COOL_Is_Expedite") == 0 ) ? true : false );
                lineItem.setCustomPriceField1(rs.getDouble("COOL_CUSTOM_PRICE_FIELD1"));
                lineItem.setCustomTextField1(rs.getString("COOL_CUSTOM_TEXT_FIELD1"));

                loadMarketingAndMfgPartNumbers ( lineItem );

                loadHoldsForLineItem ( lineItem );

                lineItems.add ( lineItem );
            }
            success = true;
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get present lineItems Info from database for splitting", sqlex );
        }
        finally
         {
             if ( dbTransaction == null )
             {
                 try
                 {
                     if ( conn != null )
                     {
                         if ( success == true )
                             conn.commit ();
                         else
                             conn.rollback ();
                     }
                 }
                 catch ( SQLException ignore )
                 {
                 }
             }

             if (rs != null) {
                 try { rs.close(); } catch (SQLException excp) {}
             }

             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException excp) {}
             }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }

        }
        return lineItems;
    }

   //To display  LineItems that are Issued and Partially Shipped also
   public static ArrayList loadAllChangeOrderLineItems(DBTransaction dbTransaction,int salesOrderId) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList lineItems = new ArrayList();

        int lineItemNo;
        int poLineItemNo;
        int cancelStatus;

        String coolStatus = null;
        int isCancel;

        if(salesOrderId == -1)
            return null;

        try {
            if (dbTransaction == null) {
                    conn = DataSource.getConnection();
            }
            else {
                    conn = dbTransaction.getConnection();
            }
            //Load all the open order lineItems
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAllChangeOrderLineItems"));
            pstmt.setInt (1, salesOrderId);

            rs = pstmt.executeQuery();
            LineItem lineItem = null;

            while(rs.next()) {
                lineItem = new LineItem();

                lineItem.setSalesOrderId (salesOrderId);
                lineItem.setLineItemId(rs.getInt("LineItem_ID"));

                lineItem.setLineItemNo(rs.getInt("COOL_Line_Item_No"));
                lineItem.setPoLineItemNo(rs.getInt("COOL_PO_Line_Item_No"));

                lineItem.setProductId(rs.getInt("Product_ID"));

                lineItem.setLineItemDescription(rs.getString("COOL_Description"));

                lineItem.setQuantity(rs.getInt("COOL_Qty"));
				lineItem.setUnitPriceAsDouble(rs.getDouble("COOL_Unit_Cost"));
				lineItem.setAmountAsDouble(rs.getDouble("COOL_Amount"));

                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COOL_Requested_Date"),null));
                lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_With_Substitutables"),null));
                lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_Without_Substitutables"),null));
                lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("COOL_Schedule_Date"),null));
                lineItem.setRevisedDate(StringUtils.formatDateToString(rs.getDate("COOL_Revised_Date"),null));

                lineItem.setCustomerPartNo(rs.getString("Customer_PartNumber"));

                lineItem.setLabelNumber(rs.getString("Label_Number") );
                cancelStatus = rs.getInt( "COOL_Is_Cancel" );

                lineItem.setIsCancel( ( cancelStatus == 0 ) ? true : false );
                lineItem.setCancelStatus( ( cancelStatus == 0 ) ? true : false);

                isCancel = cancelStatus ;
                if( isCancel == 0 )
                {
                    coolStatus = COOL_STATUS;
                }
                else
                {
                    coolStatus = rs.getString( "COOL_Status" );
                }
                lineItem.setStatus( coolStatus );
                lineItem.setOldStatus(rs.getString( "COOL_Status" ));
                lineItem.setIsTaxable((rs.getInt("COOL_Taxable") == 0) ? true : false);
                lineItem.setSalesOrderNo (rs.getString ("SalesOrder_No"));
                lineItem.setFinalStatus(rs.getString("Final_COOL_Status"));

                lineItem.setIssueStatus(rs.getString("Issue_Status"));

                lineItem.setIsExpedite( (rs.getInt("COOL_Is_Expedite")==0) ? true : false);
                lineItem.setCustomPriceField1(rs.getDouble("COOL_CUSTOM_PRICE_FIELD1"));
                lineItem.setCustomTextField1(rs.getString("COOL_CUSTOM_TEXT_FIELD1"));


                String suggDate = lineItem.getSuggScheduleDateWithoutSubstitutables();
                lineItem.setSuggScheduleDate (suggDate);

                String scheduleDate = lineItem.getScheduleDate();
                String revisedDate = lineItem.getRevisedDate();

                if ((scheduleDate == null) || (scheduleDate.trim().length() == 0)) {
                    lineItem.setScheduleDate (suggDate);
                }

                loadMarketingAndMfgPartNumbers(lineItem);

                loadHoldsForLineItem(lineItem);

                lineItems.add( lineItem );
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get present lineItems Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return lineItems;
   }



   public static ArrayList loadCancelledOrderLineItemsByCustomerId(int salesOrderId,int customerId) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList lineItems = new ArrayList();

        int odmId = - 1;

        if(salesOrderId == -1)
            return null;

        try {
            conn = DataSource.getConnection();
            //Load all the open order lineItems

            /*pstmt = conn.prepareStatement("SELECT SO_Odm_id from Sales_Order where salesOrder_Id = ?");
            pstmt.setInt (1, salesOrderId);
            rs = pstmt.executeQuery();
            if(rs.next() ){
                odmId = rs.getInt("SO_Odm_id");
            }
            rs.close() ;
            pstmt.close();


            if( odmId <= 0 )
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadCancelledOrderLineItemsByCustomerId"));
                pstmt.setInt (1, customerId);
	            pstmt.setInt (2, salesOrderId);
            }
            else{
                pstmt = conn.prepareStatement ("SELECT Customer_Cancel_Order_LineItem.*, Customer_PartNumber,Label_Number FROM Customer_Cancel_Order_LineItem left outer join Customer_PartNumbers on  Customer_Cancel_Order_LineItem.product_Id =Customer_PartNumbers.product_Id and Customer_PartNumbers.customer_id = ? and ODM_ID = ? WHERE SalesOrder_ID = ?  order by ccol_line_item_no");
                pstmt.setInt (1, customerId);
                pstmt.setInt (2, odmId );
                pstmt.setInt (3, salesOrderId );

            }

	        pstmt.setInt (1, customerId);
	        pstmt.setInt (2, salesOrderId);*/
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadCancelledOrderLineItems"));
            pstmt.setInt (1, salesOrderId);
            rs = pstmt.executeQuery();
            LineItem lineItem = null;

            rs = pstmt.executeQuery();
            while(rs.next()) {
                lineItem = new LineItem();
                lineItem.setLineItemId(rs.getInt("LineItem_ID"));
                lineItem.setLineItemNo(rs.getInt("CCOL_Line_Item_No"));
                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("CCOL_Qty"));
                lineItem.setIsTaxable((rs.getInt("CCOL_Taxable") == 0) ? true : false);
				lineItem.setUnitPriceAsDouble(rs.getDouble("CCOL_Unit_Cost"));
				lineItem.setAmountAsDouble(rs.getDouble("CCOL_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("CCOL_Requested_Date"),null));

                lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("CCOL_Schedule_Date"),null));

                lineItem.setStatus("CANCELLED");
                lineItem.setChangeNumber(rs.getInt("CCOL_Change_Number"));

		        lineItem.setCustomerPartNo(rs.getString("CCOL_Customer_PartNumber"));
                //lineItem.setLabelNumber(rs.getString("Label_Number") );

                loadCancelledMarketingAndMfgPartNumbers(lineItem);

                lineItems.add(lineItem);
            }

        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get cancelled lineItems Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return lineItems;
   }



   /**
    * Load all the order line items to show
    * @param salesOrderId
    * @return LineItems (ArrayList)
    * To call the newly added stored procedure sp_load_only_change_order_lineitems
    */
   public static ArrayList loadOnlyChangeOrderLineItems(int salesOrderId) throws ScalarException
   {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        ArrayList lineItems = new ArrayList();

        if(salesOrderId == -1)
            return null;

        try {
            conn = DataSource.getConnection();
            cstmt = conn.prepareCall (SqlQueryMappings.get ("sales.salesOrder.loadChangeOrderLineItemsForView"));
            cstmt.setInt( 1, salesOrderId );

            rs = cstmt.executeQuery();
            LineItem lineItem = null;

            while(rs.next()) {
                lineItem = new LineItem();
                lineItem.setLineItemId(rs.getInt("LineItem_ID"));
                lineItem.setLineItemNo(rs.getInt("CCO_Line_Item_No"));
                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("CCO_Qty"));
                lineItem.setIsTaxable((rs.getInt("CCO_Taxable") == 0) ? true : false);
				lineItem.setUnitPriceAsDouble(rs.getDouble("CCO_Unit_Cost"));
				lineItem.setAmountAsDouble(rs.getDouble("CCO_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("CCO_Requested_Date"),null));
                lineItem.setStatus("CHANGED");
                lineItem.setChangeNumber(rs.getInt("CCO_Change_Number"));
                lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("CCO_Schedule_Date"),null));
                lineItem.setPoLineItemNo(rs.getInt("CCO_PO_Line_Item_No"));
		        lineItem.setCustomerPartNo(rs.getString("Customer_PartNumber")); //Added by Sarada on 20th Jan 2004 to get Customer partNumber
                lineItem.setLabelNumber(rs.getString("Label_Number") );
                lineItem.setCustomPriceField1(rs.getDouble("CCO_CUSTOM_PRICE_FIELD1"));
                lineItem.setCustomTextField1(rs.getString("CCO_CUSTOM_TEXT_FIELD1"));
                loadMarketingAndMfgPartNumbers(lineItem);
                lineItems.add(lineItem);
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get cancelled lineItems Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return lineItems;
   }


    public static void updateLineItemSuggestedDates (DBTransaction dbTransaction, Iterator linteItems) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        if (linteItems == null) {
            return;
        }

        try {

            if(dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.updateLineItemSuggestedDates"));

            LineItem lineItem = null;

            while (linteItems.hasNext()) {
                lineItem = (LineItem) linteItems.next();

                pstmt.setString (1, lineItem.getSuggScheduleDate());
                pstmt.setInt (2, lineItem.getLineItemId());

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update line item suggested schedule dates into database", sqle);
        }

        finally {

            if (dbTransaction == null) {
            	try {
                    if (conn != null) {
                        conn.commit();
                    }
                }
                catch (SQLException ignore) {}
            }


            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }



    public static void updateLineItemSuggestedDateAndSystemDate (DBTransaction dbTransaction, String lineItemNumber, String salesOrderId, String suggDate, String scheduleDate) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;

        try {

            if(dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            java.sql.Date suggSQLDate = null;

            if ((suggDate != null) && (suggDate.trim().length() != 0)) {
                suggSQLDate = new java.sql.Date (StringUtils.format (suggDate).getTime());

            }

            java.sql.Date scheduleSQLDate = null;

            if ((scheduleDate != null) && (scheduleDate.trim().length() != 0)) {
                scheduleSQLDate = new java.sql.Date (StringUtils.format (scheduleDate).getTime());

            }

            pstmt = conn.prepareStatement ( "SELECT COOL_REVISED_DATE FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = ? AND COOL_LINE_ITEM_NO = ?" );
            pstmt.setInt ( 1, Integer.parseInt (salesOrderId) );
            pstmt.setInt ( 2, Integer.parseInt (lineItemNumber) );
            rset = pstmt.executeQuery();
            java.sql.Date revisedDate = null;
            if ( rset.next() )
            {
                revisedDate = rset.getDate( "COOL_REVISED_DATE" );
            }
            try
            {
                rset.close();
                pstmt.close();
            }catch ( SQLException ignore ) {}

            if ( revisedDate == null )
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.updateLineItemSuggestedDateAndSystemDate"));
                pstmt.setDate (1, suggSQLDate);
                pstmt.setDate (2, scheduleSQLDate);
                pstmt.setInt (3, Integer.parseInt (lineItemNumber));
                pstmt.setInt (4, Integer.parseInt (salesOrderId));

                pstmt.execute();

                //To update schedule date in the Customer_Change_Order_LineItem table
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.salesOrder.updateCCOScheduleDate" ) );
                pstmt.setDate ( 1, scheduleSQLDate );
                pstmt.setInt ( 2, Integer.parseInt ( lineItemNumber ) );
                pstmt.setInt ( 3, Integer.parseInt ( salesOrderId ) );
                pstmt.setInt ( 4, Integer.parseInt ( lineItemNumber ) );
                pstmt.setInt ( 5, Integer.parseInt ( salesOrderId ) );

                pstmt.execute();
            }
            else //NEED TO UPDATE THE REVISED DATE
            {
                pstmt = conn.prepareStatement ("update customer_open_order_lineitem set COOL_Sugg_Schedule_Date_Without_Substitutables = ?, COOL_REVISED_DATE = ? where COOL_Line_Item_No = ? and SalesOrder_ID = ?");
                pstmt.setDate (1, suggSQLDate);
                pstmt.setDate (2, scheduleSQLDate);
                pstmt.setInt (3, Integer.parseInt (lineItemNumber));
                pstmt.setInt (4, Integer.parseInt (salesOrderId));

                pstmt.execute();

                //To update schedule date in the Customer_Change_Order_LineItem table
                pstmt = conn.prepareStatement ( "UPDATE Customer_Change_Order_LineItem SET CCO_REVISED_DATE = ? WHERE ChangeItem_ID = ( select ChangeItem_ID from Customer_Change_Order_LineItem where changeNo = ( select max(changeNo) from Customer_Change_Order_LineItem where CCO_Line_Item_No = ? and SalesOrder_Id = ? ) and CCO_Line_Item_No = ? and SalesOrder_Id = ? )" );
                pstmt.setDate ( 1, scheduleSQLDate );
                pstmt.setInt ( 2, Integer.parseInt ( lineItemNumber ) );
                pstmt.setInt ( 3, Integer.parseInt ( salesOrderId ) );
                pstmt.setInt ( 4, Integer.parseInt ( lineItemNumber ) );
                pstmt.setInt ( 5, Integer.parseInt ( salesOrderId ) );
                
                pstmt.execute();
            }
            if (dbTransaction == null)
            {
                if (conn != null)
                {
                    conn.commit();
                }
            }
            success = true;
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update line item suggested schedule date-" + suggDate + " for lineItem# " + lineItemNumber + ", salesOrder ID: " + salesOrderId, sqle);
        }

        finally
        {
            if ( !success )
            {
                if ( dbTransaction == null && conn != null )
                {
                    try { conn.rollback(); } catch ( SQLException ignore ) {}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    public static LineItem loadOrderLineItem (String lineItemNumberStr, String salesOrderIdStr) throws ScalarException
    {

        int lineItemNumber = -1;
        int salesOrderId = -1;

        try
        {
            lineItemNumber = Integer.parseInt (lineItemNumberStr);
            salesOrderId = Integer.parseInt (salesOrderIdStr);
        }
        catch (NumberFormatException nfe) {
            // invalid values
        }
        LineItem lineItem = loadOrderLineItem (lineItemNumber, salesOrderId);
        if ( lineItem != null )
        {
            if ( lineItem.getRevisedDate() != null && !"".equals( lineItem.getRevisedDate() ) )
            {
                lineItem.setScheduleDate( lineItem.getRevisedDate() );
            }
        }
        return lineItem;
    }

    public static LineItem loadOrderLineItem (int lineItemNumber, int salesOrderId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        LineItem lineItem = null;

        if((lineItemNumber == -1) || (salesOrderId == -1)) {
            return null;
        }

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadOrderLineItem"));
            pstmt.setInt (1, lineItemNumber);
            pstmt.setInt (2, salesOrderId);

            rs = pstmt.executeQuery();

            if (rs.next()) {

                lineItem = new LineItem();

                lineItem.setSalesOrderId (salesOrderId);
                lineItem.setLineItemId(rs.getInt("LineItem_ID"));
                lineItem.setLineItemNo(rs.getInt("COOL_Line_Item_No"));
                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("COOL_Qty"));
                lineItem.setUnitPrice(rs.getFloat("COOL_Unit_Cost"));
                lineItem.setAmount(rs.getFloat("COOL_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COOL_Requested_Date"),null));
                lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_With_Substitutables"),null));
                lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_Without_Substitutables"),null));
                lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("COOL_Schedule_Date"),null));
                lineItem.setRevisedDate(StringUtils.formatDateToString(rs.getDate("COOL_Revised_Date"),null));
                lineItem.setStatus(rs.getString("COOL_Status"));
                lineItem.setIsTaxable((rs.getInt("COOL_Taxable") == 0) ? true : false);
                lineItem.setSalesOrderNo (rs.getString ("SalesOrder_No"));

                String suggDate = lineItem.getSuggScheduleDateWithoutSubstitutables();

                lineItem.setSuggScheduleDate (suggDate);

                String scheduleDate = lineItem.getScheduleDate();
                if ((scheduleDate == null) || (scheduleDate.trim().length() == 0)) {
                    lineItem.setScheduleDate (suggDate);
                }

                loadMarketingAndMfgPartNumbers(lineItem);
            }


        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load line Item for lineItem Number: " + lineItemNumber + ", salesOrder ID: " + salesOrderId, sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return lineItem;
   }

  public static HashMap loadOrderLineItemsMap (int salesOrderId) throws ScalarException

   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        HashMap lineItems = new HashMap();

        if(salesOrderId == -1)
            return null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadOrderLineItems"));

            pstmt.setInt (1, salesOrderId);

            rs = pstmt.executeQuery();
            LineItem lineItem = null;
            int lineItemId = -1;

            while(rs.next()) {
                lineItem = new LineItem();

                lineItemId = rs.getInt("LineItem_ID");

                lineItem.setSalesOrderId (salesOrderId);

                lineItem.setLineItemId(lineItemId);
                lineItem.setLineItemNo(rs.getInt("COOL_Line_Item_No"));
				lineItem.setPoLineItemNo(rs.getInt("COOL_PO_Line_Item_No"));

                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("COOL_Qty"));

                lineItem.setUnitPriceAsDouble(rs.getDouble("COOL_Unit_Cost"));
                lineItem.setAmount(rs.getFloat("COOL_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COOL_Requested_Date"),null));
                lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_With_Substitutables"),null));
                lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_Without_Substitutables"),null));
                lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("COOL_Schedule_Date"),null));
                lineItem.setRevisedDate(StringUtils.formatDateToString(rs.getDate("COOL_Revised_Date"),null));
                lineItem.setStatus(rs.getString("COOL_Status"));
                lineItem.setIsTaxable((rs.getInt("COOL_Taxable") == 0) ? true : false);
                lineItem.setCustomPriceField1(rs.getDouble("COOL_CUSTOM_PRICE_FIELD1"));
                lineItem.setCustomTextField1(rs.getString("COOL_CUSTOM_TEXT_FIELD1"));
                lineItem.setSalesOrderNo (rs.getString ("SalesOrder_No"));

                loadMarketingAndMfgPartNumbers(lineItem);

                //Add the lineItem
                lineItems.put(lineItemId+"", lineItem);
            }


        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get present lineItems Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return lineItems;
   }

     //To get customer part numbers also
      public static HashMap loadOrderLineItemsWithCustomerPartNos (int salesOrderId, int customerId) throws ScalarException
      {
           Connection conn = null;
           PreparedStatement pstmt = null;
           ResultSet rs = null;
           HashMap lineItems = new HashMap();
           //int odmId = -1;

           if(salesOrderId == -1)
               return null;

           try {
               conn = DataSource.getConnection();
               //Load all the open order lineItems
               //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadOrderLineItems"));
               //Above query is modified by sarada on 28th Jan 2004 to get customer part numbers also
               //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadOrderLineItemsByCustomerId"));
               //pstmt.setInt (1, customerId);
               //pstmt.setInt (2, salesOrderId);


                /*pstmt = conn.prepareStatement("SELECT SO_Odm_id from Sales_Order where salesOrder_Id = ?");
                pstmt.setInt (1, salesOrderId);
                rs = pstmt.executeQuery();

                if(rs.next() )
                {
                    odmId = rs.getInt("SO_Odm_id");
                }
                rs.close() ;
                pstmt.close();

                //Load all the open order lineItems
                if( odmId <= 0 )
                {
                    pstmt = conn.prepareStatement ("SELECT Customer_Open_Order_LineItem.* ,Sales_Order.SalesOrder_No ,Customer_Partnumber,Label_Number FROM  Customer_Open_Order_LineItem left outer join Customer_Partnumbers on  Customer_Open_Order_LineItem.product_Id =Customer_PartNumbers.product_Id and Customer_PartNumbers.customer_id = ? and Customer_PartNumbers.odm_id is Null,Sales_Order WHERE   Customer_Open_Order_LineItem.salesOrder_id = Sales_Order.salesOrder_id and Sales_Order.salesOrder_id = ?");
                    pstmt.setInt (1, customerId);
                    pstmt.setInt (2, salesOrderId);
                }
                else
                {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadOrderLineItemsByCustomerId"));
                    pstmt.setInt (1, customerId);
                    pstmt.setInt (2, odmId );
                    pstmt.setInt (3, salesOrderId );
                }*/
               pstmt = conn.prepareStatement ("SELECT Customer_Open_Order_LineItem.* ,Sales_Order.SalesOrder_No FROM  Customer_Open_Order_LineItem ,Sales_Order WHERE   Customer_Open_Order_LineItem.salesOrder_id = Sales_Order.salesOrder_id and Sales_Order.salesOrder_id = ?");
               pstmt.setInt (1, salesOrderId);

               rs = pstmt.executeQuery();
               LineItem lineItem = null;
               int lineItemId = -1;

               while(rs.next()) {
                   lineItem = new LineItem();

                   lineItemId = rs.getInt("LineItem_ID");

                   lineItem.setSalesOrderId (salesOrderId);

                   lineItem.setLineItemId(lineItemId);
                   lineItem.setLineItemNo(rs.getInt("COOL_Line_Item_No"));

                   lineItem.setPoLineItemNo(rs.getInt("COOL_PO_Line_Item_No"));

                   lineItem.setProductId(rs.getInt("Product_ID"));
                   lineItem.setQuantity(rs.getInt("COOL_Qty"));

                   lineItem.setUnitPriceAsDouble(rs.getDouble("COOL_Unit_Cost"));
                   lineItem.setAmountAsDouble(rs.getDouble("COOL_Amount"));

                   lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COOL_Requested_Date"),null));
                   lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_With_Substitutables"),null));
                   lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_Without_Substitutables"),null));
                   lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("COOL_Schedule_Date"),null));
                   lineItem.setRevisedDate(StringUtils.formatDateToString(rs.getDate("COOL_Revised_Date"),null));
                   lineItem.setStatus(rs.getString("COOL_Status"));
                   lineItem.setIsTaxable((rs.getInt("COOL_Taxable") == 0) ? true : false);
                   lineItem.setSalesOrderNo (rs.getString ("SalesOrder_No"));
                   lineItem.setCustomerPartNo (rs.getString ("COOL_Customer_Partnumber"));
                   lineItem.setCustomPriceField1(rs.getDouble("COOL_CUSTOM_PRICE_FIELD1"));
                   lineItem.setCustomTextField1(rs.getString("COOL_CUSTOM_TEXT_FIELD1"));

                   loadMarketingAndMfgPartNumbers(lineItem);

                   lineItems.put(lineItemId+"", lineItem);
               }
           }
           catch (Exception sqlex) {
               sqlex.printStackTrace();
               throw new ScalarException ("Could not get present lineItems Info from database", sqlex);
           }
           finally {
               if (rs != null) {
                   try { rs.close(); } catch (SQLException excp) {}
               }
               if (pstmt != null) {
                   try { pstmt.close(); } catch (SQLException excp) {}
               }
               if (conn != null) {
                   try { conn.close(); } catch (SQLException excp) {}
               }
           }

           return lineItems;
      }


   /**
    * Load the Marketing Part Number & Mfg Part Number
    * @param lineItem
    * @return LineItem
    */
   public static LineItem loadMarketingAndMfgPartNumbers(LineItem lineItem) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        if(lineItem == null)
            return null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadMarketingAndMfgPartNumbers"));

            pstmt.setInt (1, lineItem.getLineItemId());

            rs = pstmt.executeQuery();
            while(rs.next()) {

                lineItem.setManufacturingPartNo(rs.getString("Part_Number"));
                lineItem.setMarketingPartNo(rs.getString("P_Marketing_Part_Number"));
                lineItem.setDescription(rs.getString("P_Comments"));

            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get part number for this product", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

       //Checking if the mfg part number has been retrieved else calling the method to fetch the mfg part no from the cancelled items
       if(lineItem.getManufacturingPartNo()==null)
           return loadCancelledMarketingAndMfgPartNumbers(lineItem);
       else
           return lineItem;
    }

    public static LineItem loadHoldsForLineItem(LineItem lineItem) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        if(lineItem == null)
            return null;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadHoldsForLineItem"));
            pstmt.setInt (1, lineItem.getLineItemId());

            rs = pstmt.executeQuery();
            String holdByGroup = null;
            String holdReasonCode = null;

            while(rs.next()) {

                holdByGroup = rs.getString("Hold_By_Group");
                holdReasonCode = rs.getString("Hold_Reason_Code");

                if("OPERATIONS".equalsIgnoreCase (holdByGroup)) {
                    lineItem.setOperationsHold (holdReasonCode);
                }

                else if ("SALES".equalsIgnoreCase (holdByGroup)) {
                    lineItem.setSalesHold (holdReasonCode);
                }

                else if ("FINANCE".equalsIgnoreCase (holdByGroup)) {
                    lineItem.setFinanceHold (holdReasonCode);
                }

                else if("MARKETING".equalsIgnoreCase (holdByGroup)) {
                    lineItem.setMarketingHold (holdReasonCode);
                }

            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get part number for this product", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return lineItem;
   }

    
    public static ArrayList loadApproversList( DBTransaction dbTransaction ) throws ScalarException
    {
        ArrayList approversList = new ArrayList();
        try
		{
	        approversList.add( SalesDAO.loadFinanceApprovers(dbTransaction) );
	        approversList.add( SalesDAO.loadMarketingApprovers(dbTransaction) );
	        approversList.add( SalesDAO.loadSalesApprovers(dbTransaction) );
	        approversList.add( SalesDAO.loadOperationsApprovers(dbTransaction) );
		}
        catch( Exception e )
		{
        	e.printStackTrace();
            throw new ScalarException ("Could not load the approvers for the Sales Order", e);
		}
        return approversList;
    }

   public static Alarm createAlarm (DBTransaction dbTransaction, User user, SalesOrder salesOrder, String host ) throws ScalarException
   {
        Alarm newAlarm          = null;

        int salesOrderId        = salesOrder.getSalesOrderId();
        String salesOrderNo     = salesOrder.getSalesOrderNo();
        String comments         = salesOrder.getComments();
        ArrayList approversList = null;
        ArrayList list          = null;
        User userBean           = null;

        try
        {
            // CREATE ORIGINATOR AND RECIPIENTS
            ArrayList recipients = new ArrayList();
            AlarmUser from = new AlarmUser ( user.getUserId(), user.getEmail() );

            // SET EXPIRE DATE
            Calendar cal = Calendar.getInstance();
            cal.add( Calendar.DAY_OF_YEAR, ConfigManager.getSalesOrderExpiresAfterDays() );
            Date expiresOn = cal.getTime();

            //This needs to be modified, if there is no need to send mail, if system automatically approves
            approversList = loadApproversList( dbTransaction );
            
            AlarmUser to = null;
            for( int i = 0; i < approversList.size(); i++ )
            {
                list = ( ArrayList ) approversList.get( i );

                for( int j = 0; j < list.size(); j++ )
                {
                    userBean = ( User ) list.get( j );

                    to = new AlarmUser( userBean.getUserId(), userBean.getEmail() );
                    recipients.add( to );
                }
            }
            if( salesOrder.getIsSample() )
            {
                newAlarm = new Alarm ("Sample Order Notification - SampleOrder Number: " + salesOrderNo, SAMPLE_ORDER_ALARM,  from);
            }
            else
            {
                newAlarm = new Alarm ("Sales Order Notification - SalesOrder Number: " + salesOrderNo, SALES_ORDER_ALARM,  from);
            }
            newAlarm.setPriority( Alarm.ALARM_PRIORITY_HIGH );
            newAlarm.setExpiresOn( expiresOn.getTime() );
            // set the new recipients to the alarm
            newAlarm.setRecipients ( recipients );
            
            // ACTIVATE
            newAlarm.activate( dbTransaction );
            
            updateSalesOrderFields( dbTransaction, "UPDATE Sales_Order SET SO_Is_Pending=0, Alarm_ID = " +  newAlarm.getId() + " WHERE SalesOrder_ID = " + salesOrderId );

            // to remove users who are currently disabled and for whom Notifications not required for mail notifications
            recipients.clear();
            for ( int i = 0; i < approversList.size(); i++ )
            {
                list = ( ArrayList ) approversList.get ( i );

                for ( int j = 0; j < list.size (); j++ )
                {
                    userBean = ( User ) list.get ( j );
                    //if ( ( userBean.getReceiveNotifications ().equals ( "on" ) ) && ( userBean.getDisabled () == false ) )
                    if ( userBean.getSalesAckMail() && userBean.getDisabled() == false )
                    {
                        to = new AlarmUser ( userBean.getUserId (), userBean.getEmail () );
                        recipients.add ( to );
                    }
                }
            }
            // set the new recipients to the alarm
            newAlarm.setRecipients ( recipients );
        }
        catch(Exception ex) {
            ex.printStackTrace();
            throw new ScalarException ("Could not create alarm", ex);
        }

       return newAlarm;
   }


   public static void sendMailToCustomer(int salesOrderId) throws ScalarException
   {
        Connection conn = null;
        CallableStatement cstmt = null;

        try {

            conn = DataSource.getConnection();
            cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.sendMailToCustomer"));
            cstmt.registerOutParameter(1, Types.INTEGER); //get the status of order
            cstmt.setInt (2, salesOrderId);
            cstmt.registerOutParameter(3, Types.VARCHAR);

            cstmt.execute();
            int isApproved = cstmt.getInt(1);

            if(isApproved == 0) {
                SalesOrder salesOrder = loadSalesOrder(Integer.toString(salesOrderId));
                String to = cstmt.getString(3);

                String from = ConfigManager.getSysAdminEmail();
                String subject = "Approved Sales Order";
	            String message = formatMessage(salesOrder,SalesOrderDAO.loadOrderLineItemsByCustomerId(salesOrder.getSalesOrderId(),salesOrder.getCustomerId()));

                Mailer.send (from, to, subject, message, Mailer.CONTENT_TYPE_HTML);

            }

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not send mail to customer", sqle);
        }
        finally {
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

   }

   private static String formatMessage(SalesOrder salesOrder, ArrayList lineItems) throws ScalarException
   {
       StringBuffer message = new StringBuffer();

        String shippingMethodLabel = null;
        try
        {
            shippingMethodLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.salesOrder.ShippingMethod" );
            if ( shippingMethodLabel == null )
            {
                shippingMethodLabel = Labels.DEFAULT_SHIPPING_METHOD_LABEL;
            }
        }
        catch ( NullPointerException e )
        {
            shippingMethodLabel    = Labels.DEFAULT_SHIPPING_METHOD_LABEL;
        }
       String shippingAccountLabel = null;
       try
       {
           shippingAccountLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.salesOrder.ShippingAccount" );
           if ( shippingAccountLabel == null )
           {
               shippingAccountLabel = Labels.DEFAULT_SHIPPING_ACCOUNT_LABEL;
           }
       }
       catch ( NullPointerException e )
       {
           shippingAccountLabel = Labels.DEFAULT_SHIPPING_ACCOUNT_LABEL;
       }
       message.append ("<table border='0' width='100%' align=right>\n");
       message.append ("<th colspan='4'><h2>Sales Order Details</h2></th>\n");
       message.append ("<tr><td width='20%'><b>Customer PO</b></td>");
       message.append ("<td width='40%'>" + salesOrder.getCustomerPO() + "</td>");
       message.append ("<td width='20%'><b>Sales Person</b></td>");
       message.append ("<td width='20%'>" + salesOrder.getSalesPerson() + "</td></tr>");
       message.append ("<tr><td width='20%'><b>"+shippingMethodLabel+"</b></td>");
       message.append ("<td width='40%'>" + salesOrder.getShippingMethod() + "</td>");
       message.append ("<td width='20%'><b>"+shippingAccountLabel+"</b></td>");
       message.append ("<td width='20%'>" + salesOrder.getShippingAccount() + "</td></tr>");
       message.append ("<tr><td width='20%'><b>Billing Address</b></td>");
       message.append ("<td width='40%'>" + salesOrder.getBillingAddress() + "</td>");
       message.append ("<td width='20%'><b>Sales Person</b></td>");
       message.append ("<td width='20%'>" + salesOrder.getShippingAddress() + "</td></tr>");

       message.append ("<tr><td colspan='4'><table border=1 width='100%' align=center>");
       message.append ("<tr><td width='8%' align=center class='NArialL'>Line Item</td>");
       message.append ("<td width='20%' align=center class='NArialL'>Product</td>");
       message.append ("<td width='5%' align=center class='NArialL'>Quantity</td>");
       message.append ("<td width='15%' align=center class='NArialL'>Request Date</td>");
       message.append ("<td width='10%' align=center class='NArialL'>Price</td>");
       message.append ("<td width='15%' align=center class='NArialL'>Amount</td></tr>");

       for(int i=0; i<lineItems.size(); i++)
       {
           LineItem lineItem = (LineItem)lineItems.get(i);

            message.append ("<tr><td width='8%' align=center class='NArialL'>"+ lineItem.getLineItemNo() +"</td>");
            message.append ("<td width='20%' align=center class='NArialL'>"+ lineItem.getMarketingPartNo() + "</td>");
            message.append ("<td width='5%' align=center class='NArialL'>" + lineItem.getQuantity() + "</td>");
            message.append ("<td width='15%' align=center class='NArialL'>" + lineItem.getRequestDate() + "</td>");
            message.append ("<td width='10%' align=center class='NArialL'>" + lineItem.getUnitPrice() + "</td>");
            message.append ("<td width='15%' align=center class='NArialL'>" + lineItem.getAmount() + "</td></tr>");

       }

       message.append("</table></td></tr></table>");

       return message.toString();
   }

   public static boolean isCustomerPOExists(int customerId, String customerPO) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean isExists = false;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.isCustomerPOExists"));
            pstmt.setInt (1, customerId);
            pstmt.setString (2, customerPO);

            rs = pstmt.executeQuery();

            if (rs.next() == false) {
                isExists = false;
            }
            else {
        	isExists = true;
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get CustomerPO for Sales Order Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return isExists;

   }

	/**
	* Method added To know whether the Sales Order No already exists
	**/

    public static boolean isSalesOrderNoExists(String salesOrderNo) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean isExists = false;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.isSalesOrderExists"));
            pstmt.setString (1, salesOrderNo);
            rs = pstmt.executeQuery();

            if (rs.next() == false) {
                isExists = false;
            }
            else {
        	isExists = true;
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get SalesOrderNo for Sales Order Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return isExists;

   }

    /**
    * Save OnlineSalesOrder details
    * @param salesOrder
    * @return salesOrderId
    */
   public static int saveOnlineOrder(DBTransaction dbTransaction , SalesOrder salesOrder, String userId)
                        throws ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       boolean success = false;
       int salesOrderId = -1;

       try {

         // Get the connection from regular datasource if its not in a manual transaction
         if (dbTransaction == null) {
                conn = DataSource.getConnection();
         }
         else {
                conn = dbTransaction.getConnection();
         }

         pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.onlineOrder.insertOnlineOrder"));
         pstmt.setInt (1, salesOrder.getCustomerId());
         pstmt.setString (2, salesOrder.getCustomerPO());
         pstmt.setString (3, salesOrder.getContactPerson());
         pstmt.setString (4, salesOrder.getSalesPerson());
         pstmt.setString (5, salesOrder.getShippingMethodName());
         pstmt.setString (6, salesOrder.getShippingAccount());
         pstmt.setInt (7, salesOrder.getBillingAddressId());
         pstmt.setInt (8, salesOrder.getShippingAddressId());
         pstmt.setString (9, salesOrder.getComments());
         pstmt.setString (10, salesOrder.getShippingInstructions());
         pstmt.setString (11, salesOrder.getEndUser());
         pstmt.setString (12, userId);
         pstmt.setDate (13,new java.sql.Date (System.currentTimeMillis()));


         pstmt.executeUpdate();
         pstmt.close();
         pstmt = null;

		 pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.onlineOrder.getOnlineOrderId"));
         rs = pstmt.executeQuery();
         if (rs.next())
               salesOrderId = rs.getInt("OnlineOrder_Id");

         success = true;
       }
       catch (Exception se) {
              se.printStackTrace();
       }
       finally {
           if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return salesOrderId;
   }


   /**
    * Save OnlineOrderLineItems
    * @param lineItems
    *
    */
   public static void insertOnlineLineItems(DBTransaction dbTransaction, int salesOrderId, ArrayList lineItems,
                        String userId) throws ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       boolean success = false;

       try {

         if (dbTransaction == null) {
                conn = DataSource.getConnection();
         }
         else {
                conn = dbTransaction.getConnection();
         }
         Iterator items = lineItems.iterator();

         pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.onlineOrder.insertOnlineLineItem"));

         while(items.hasNext())
         {
              LineItem lineItem  = (LineItem) items.next();
              pstmt.setInt (1, salesOrderId);
              pstmt.setInt (2, lineItem.getLineItemNo());
              pstmt.setInt (3, lineItem.getProductId());
              pstmt.setInt (4, lineItem.getQuantity());
              pstmt.setFloat (5, lineItem.getUnitPrice());
              pstmt.setFloat (6, lineItem.getAmount());
              pstmt.setDate (7, StringUtils.getSQLDate(lineItem.getRequestDate())); //requested date
              pstmt.setDate (8, new java.sql.Date (System.currentTimeMillis())); //booking date
              pstmt.setString (9, userId);
              pstmt.setDate (10, new java.sql.Date (System.currentTimeMillis())); //last updated date

              pstmt.addBatch();
         }

         pstmt.executeBatch();


       } catch (Exception se) {
              se.printStackTrace();
              throw new ScalarException ("Could not insert lineItems Info into database", se);
       }
       finally {
           if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

   }

   public static Alarm sendMailToSales(DBTransaction dbTransaction, User user,int salesOrderId,String comments)
                                            throws ScalarException
   {
        Alarm newAlarm = null;

       try {

         // CREATE ORIGINATOR AND RECIPIENTS
         ArrayList recipients = new ArrayList();
         AlarmUser from = new AlarmUser (user.getUserId(), user.getEmail());

         AlarmUser to = null;

         // CREATE EXPIRE DATE
         Calendar cal = Calendar.getInstance();
         cal.set (Calendar.MONTH, 6);
         cal.set (Calendar.DAY_OF_MONTH, 15);
         Date expiresOn = cal.getTime();

         //This needs to be modified, if there is no need to send mail, if system automatically approves
         ArrayList list = SalesDAO.loadRegionSalesApprovers(dbTransaction,salesOrderId);  // Need to get userIds from that region...

         for (int j=0; j<list.size(); j++)
         {
                User userBean = (User)list.get(j);
                to = new AlarmUser(userBean.getUserId(),userBean.getEmail());
                recipients.add(to);
         }

          newAlarm = new Alarm ("Online Sales Order", SALES_ORDER_ALARM,  from);
          newAlarm.setPriority(Alarm.ALARM_PRIORITY_HIGH);
          newAlarm.setExpiresOn(expiresOn.getTime());
          newAlarm.setRecipients (recipients);

          // ACTIVATE
          newAlarm.activate(null);

          //Update the new AlarmId in OnlineOrder table
          updateSalesOrderFields(dbTransaction,"UPDATE Online_Order SET OO_Alarm_ID = " + newAlarm.getId() + " Where OnlineOrder_ID = " + salesOrderId);

       } catch(Exception ex) {
            ex.printStackTrace();
                throw new ScalarException ("Could not create alarm", ex);
       }

       return newAlarm;
   }


     /**
    * Load all the salesOrder details
    * @param salesOrderId
    * @return SalesOrder
    */
   public static SalesOrder loadOnlineOrder(String salesOrderId) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        SalesOrder salesOrder = null;

        if(salesOrderId == null)
            return null;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadOnlineOrder"));
            pstmt.setInt (1, Integer.parseInt(salesOrderId));

            rs = pstmt.executeQuery();
            if(rs.next() == false) {
                //No details has been obtained for this salesOrder
            } else {
                salesOrder = new SalesOrder(
                            rs.getInt ("OnlineOrder_ID"),
                            rs.getInt ("Customer_ID"),
                            rs.getString ("OO_Customer_PO"),
                            rs.getString ("OO_Contact_Person"),
                            rs.getString ("OO_Sales_Person"),
                            rs.getString ("OO_Shipping_Method"),
                            rs.getString ("OO_Shipping_Account"),
                            rs.getInt ("OO_Billing_Address_ID"),
                            rs.getInt ("OO_Shipping_Address_ID"),
                            rs.getString ("OO_Comments"),
                            rs.getString ("OO_Shipping_Instructions"),
                            rs.getString ("OO_EndUser"),
                            rs.getDate("SO_Booking_Date"),
                            false);

                  salesOrder.setCustomerName(CustomerDAO.loadCustomerName(salesOrder.getCustomerId()));
                  Address address = CustomerDAO.getAddress(salesOrder.getBillingAddressId());
                  if(address != null) {
                    salesOrder.setBillingAddress(address.getAddress1() + "\n" +
                                                address.getAddress2() + "\n" +
                                                address.getCity() + " , " +
                                                address.getState() + " ," +
                                                address.getZipCode() + "\n" +
                                                address.getCountry());
                    salesOrder.setPhoneNumber(address.getPhoneNumber());
                    salesOrder.setFaxNumber(address.getFaxNumber());
                    salesOrder.setEmail(address.getEmail());
                  }

                  address = CustomerDAO.getAddress(salesOrder.getShippingAddressId());
                  if(address != null) {
                    salesOrder.setShippingAddress(address.getAddress1() + "\n" +
                                                address.getAddress2() + "\n" +
                                                address.getCity() + " , " +
                                                address.getState() + " ," +
                                                address.getZipCode() + "\n" +
                                                address.getCountry());
                  }
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get OnlineOrder Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salesOrder;

   }

    /**
    * Load all the order line items to show
    * @param salesOrderId
    * @return LineItems (ArrayList)
    */
   public static ArrayList loadOnlineOrderLineItems(int salesOrderId) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList lineItems = new ArrayList();

        if(salesOrderId == -1)
            return null;

        try {
            conn = DataSource.getConnection();
            //Load all the open order lineItems
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadOnlineOrderLineItems"));
            pstmt.setInt (1, salesOrderId);

            rs = pstmt.executeQuery();
            LineItem lineItem = null;

            while(rs.next()) {
                lineItem = new LineItem();
                lineItem.setLineItemId(rs.getInt("OLineItem_ID"));
                lineItem.setLineItemNo(rs.getInt("OOL_LineItem_No"));
                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("OOL_Qty"));
				lineItem.setUnitPriceAsDouble(rs.getFloat("OOL_Unit_Cost"));
				lineItem.setAmountAsDouble(rs.getFloat("OOL_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("OOL_Requested_Date"),null));

                loadMarketingAndMfgPartNumbers(lineItem);

                lineItems.add(lineItem);
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get present lineItems Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return lineItems;
   }

   public static int saveOnlineSalesOrder(DBTransaction dbTransaction, int onlineOrderId, String userId) throws
                        ScalarException
   {

       Connection conn = null;
       CallableStatement cstmt = null;
       ResultSet rs = null;
       boolean success = false;
       int salesOrderId = -1;

       try {

         // Get the connection from regular datasource if its not in a manual transaction
         if (dbTransaction == null) {
                conn = DataSource.getConnection();
         }
         else {
                conn = dbTransaction.getConnection();
         }
         cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.saveOnlineSalesOrder"));
         cstmt.registerOutParameter(1, Types.INTEGER); //get the salesOrderId back
         cstmt.setInt (2, onlineOrderId);
         cstmt.setString(3, userId);

         cstmt.execute();
         salesOrderId = cstmt.getInt(1);

         success = true;
       }
       catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
           if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return salesOrderId;

   }


   public static ArrayList insertOnlineLineItems(DBTransaction dbTransaction,int onlineOrderId,
             String[] lineItemIds, String[] quantity, String[] unitPrice, String userId) throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList lineItems = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            for(int i=0; i<lineItemIds.length; i++)
            {
       			pstmt =  conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.insertOnlineLineItems"));
                pstmt.setInt(1, onlineOrderId);
                pstmt.setInt(2, Integer.parseInt(lineItemIds[i]));
                pstmt.setInt(3, Integer.parseInt(quantity[i]));
                pstmt.setFloat(4, Float.parseFloat(unitPrice[i]));
                pstmt.setString (5, userId);

                rset = pstmt.executeQuery();
                LineItem lineItem = null;

                while (rset.next()) {
                    lineItem = new LineItem();
                    lineItem.setLineItemId(rset.getInt("LineItem_Id"));
                    lineItem.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                    lineItem.setLineItemNo(rset.getInt("COOL_LineItem_No"));
                    lineItem.setProductId(rset.getInt("Product_ID"));
                    lineItem.setQuantity(rset.getInt("COOL_Qty"));
                    lineItem.setUnitPrice(rset.getFloat("COOL_Unit_Cost"));
                    lineItem.setAmount(rset.getFloat("COOL_Amount"));
                    lineItem.setRequestDate(StringUtils.formatDateToString(rset.getDate("COOL_Requested_Date"),null));

                    lineItems.add(lineItem);
                }
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert insertOnlineLineItems from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return lineItems;
   }

   public static ArrayList loadAllMarketingPartNumbers() throws ScalarException
   {

       Iterator finishedGoodsList = FinishedGoodsDAO.loadAllFinishedGoods().iterator();
       ArrayList list = new ArrayList();

       while(finishedGoodsList.hasNext()) {

            FinishedGoods finishedGoods = (FinishedGoods) finishedGoodsList.next();

            list.add(new org.apache.struts.util.LabelValueBean(
                                 finishedGoods.getMarketingPartNo(),finishedGoods.getMarketingPartNo()));
        }

       return list;
   }
   /**
    *  This is method loads all the finished goods including un-approved finished goods
    *
    * @return
    * @throws ScalarException
    */
   public static ArrayList loadMarketingPartNumbers() throws ScalarException
   {

       Iterator finishedGoodsList = FinishedGoodsDAO.loadFinishedGoods().iterator();
       ArrayList list = new ArrayList();

       while(finishedGoodsList.hasNext()) {

            FinishedGoods finishedGoods = (FinishedGoods) finishedGoodsList.next();

            list.add(new org.apache.struts.util.LabelValueBean(
                                 finishedGoods.getMarketingPartNo(),finishedGoods.getMarketingPartNo()));
        }
       return list;
   }


   public static ArrayList loadAllManfacturingPartNumbers() throws ScalarException
   {

       Iterator finishedGoodsList = FinishedGoodsDAO.loadAllFinishedGoods().iterator();
       ArrayList list = new ArrayList();
       //Added by varma 6 Jan 2004
       list.add(new org.apache.struts.util.LabelValueBean(
                                 "---- CHIPS -----","---- CHIPS -----"));
       while(finishedGoodsList.hasNext()) {

            FinishedGoods finishedGoods = (FinishedGoods) finishedGoodsList.next();
            String mfgPartNo = finishedGoods.getManufacturingPartNo();
            if((mfgPartNo != null) && (mfgPartNo.length() != 0)) {
                list.add(new org.apache.struts.util.LabelValueBean(
                                 mfgPartNo,mfgPartNo));
            }
        }

       return list;
   }

    /*To get product ids corresponding to Mfg PartNumbers */
    public static ArrayList loadAllMfgPartNumbersWithProductIds(int customerId) throws ScalarException
   {

       Iterator finishedGoodsList = FinishedGoodsDAO.loadAllFinishedGoodsWithCustomerPartNos(customerId).iterator();
       ArrayList list = new ArrayList();

       while(finishedGoodsList.hasNext())
       {
            CustomerPartNumber  customerPartNo = new CustomerPartNumber();
            FinishedGoods finishedGoods = (FinishedGoods) finishedGoodsList.next();
            String mfgPartNo = finishedGoods.getManufacturingPartNo();
            int productId = finishedGoods.getProductId();
            String custpartNumber = finishedGoods.getCustomerPartNumber();
            String labelNumber = finishedGoods.getLabelNumber() ;

           if((mfgPartNo != null) && (mfgPartNo.length() != 0))
           {
                customerPartNo.setProductId(productId);
                customerPartNo.setManufacturingPartNo(mfgPartNo);
                customerPartNo.setCustomerPartNo(custpartNumber);
                customerPartNo.setLabelNumber(labelNumber);
                customerPartNo.setOdmId(finishedGoods.getOdmId());
                list.add(customerPartNo);
            }
        }
       return list;
   }

    /* To get manufacturing part numbers for corresponding customer */
    public static ArrayList loadAllManfacturingPartNosOfCustomer( int customerId, int isSample ) throws ScalarException
   {

       Iterator finishedGoodsList = NewSalesOrderDAO.loadAllFinishedGoodsWithOdmPartNos(customerId, 0, isSample ).iterator();
       ArrayList list = new ArrayList();

       while(finishedGoodsList.hasNext())
       {
            CustomerPartNumber  customerParNo = new CustomerPartNumber();
            FinishedGoods finishedGoods = (FinishedGoods) finishedGoodsList.next();
            String mfgPartNo = finishedGoods.getManufacturingPartNo();
            int productId = finishedGoods.getProductId();
            String custpartNumber = finishedGoods.getCustomerPartNumber();

           if((mfgPartNo != null) && (mfgPartNo.length() != 0))
           {
                customerParNo.setProductId(productId);
                customerParNo.setManufacturingPartNo(mfgPartNo);
			   	customerParNo.setMarketingPartNo(finishedGoods.getMarketingPartNo());
                customerParNo.setCustomerPartNo(custpartNumber);
                list.add(customerParNo);
            }

        }

       return list;
   }
    public static ArrayList loadAllManfacturingPartNosOfCustomerWithOutobsolete( int customerId, int isSample ) throws ScalarException
  {

      Iterator finishedGoodsList = NewSalesOrderDAO.loadAllFinishedGoodsWithOdmPartNosWithOutObsolete(customerId, 0, isSample ).iterator();
      ArrayList list = new ArrayList();

      while(finishedGoodsList.hasNext())
      {
           CustomerPartNumber  customerParNo = new CustomerPartNumber();
           FinishedGoods finishedGoods = (FinishedGoods) finishedGoodsList.next();
                      
          if((finishedGoods.getManufacturingPartNo() != null) && (finishedGoods.getManufacturingPartNo().length() != 0))
          {
               customerParNo.setProductId(finishedGoods.getProductId());
               customerParNo.setManufacturingPartNo(finishedGoods.getManufacturingPartNo());
               customerParNo.setCustomerPartNo(finishedGoods.getCustomerPartNumber());
               customerParNo.setMarketingPartNo(finishedGoods.getMarketingPartNo());
               customerParNo.setComments(finishedGoods.getPComments());
               list.add(customerParNo);
           }

       }

      return list;
  }

   public static HashMap loadManfacturingPartNumbersWithCustomerPartNos( int customerId, int isSample ) throws ScalarException
   {

       Iterator finishedGoodsList = NewSalesOrderDAO.loadAllFinishedGoodsWithOdmPartNos(customerId, 0, isSample ).iterator();

       HashMap mapCustPartNos = new HashMap();
       FinishedGoods finishedGoods = null;
       while(finishedGoodsList.hasNext())
       {
             finishedGoods = (FinishedGoods) finishedGoodsList.next();
             String mfgPartNo = finishedGoods.getManufacturingPartNo();
             String custPartNos = finishedGoods.getCustomerPartNumber();
             mapCustPartNos.put( mfgPartNo, custPartNos );
        }

       return mapCustPartNos;
   }


    /*To get mfg part numbers having customer Part numbers only  */
   public static ArrayList loadOnlyFinishedGoodsHavingCustomerPartNos(int customerId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList productList = new ArrayList();

        try {
            conn = DataSource.getConnection();
	        pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.finishedgoods.loadAllFinishedGoodsWithCustomerPartNos"));
            pstmt.setInt( 1 , customerId );
            rs = pstmt.executeQuery();


           FinishedGoods finishedGoods = null;
           while(rs.next()) {

                 finishedGoods = new FinishedGoods(
                             rs.getInt("ProcessStep_ID"),
                             rs.getString("IST_Manufacturing_Part_Number"),
                             rs.getString("IST_Marketing_Part_Number"),
                             rs.getInt("Product_ID"),
                             rs.getString("P_Type"),
                             rs.getString("P_Comments"),
                     rs.getString("Customer_PartNumber"));
                finishedGoods.setLabelNumber(rs.getString("Label_Number"));
                productList.add(finishedGoods);
           }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load Finished Goods Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return productList;
    }

    public static HashMap loadManufacturingPartNumbersWithBaseOrderQty ( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        ResultSet rs            = null;
        boolean success         = false;
        HashMap mapBaseOrderQty = new HashMap ();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }
            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.process.finishedgoods.loadAllFinishedGoodsWithBaseOrderQty" ) );
            cstmt.setInt ( 1, customerId );

            rs = cstmt.executeQuery ();

            while ( rs.next () )
            {
                String mfgPartNo = rs.getString ( "IST_Manufacturing_Part_Number" );
                String baseOrderQty = rs.getString ( "Base_Order_Qty" );
                mapBaseOrderQty.put ( mfgPartNo, baseOrderQty );
            }
        }
        catch ( Exception se )
        {
            se.printStackTrace ();
        }
        finally {
            if (dbTransaction == null) {
                 try {
                     if (conn != null) {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
            if( rs != null )
            {
                try { rs.close(); } catch ( SQLException ignore ) {}
            }
             if (cstmt != null) {
                 try { cstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null) {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return mapBaseOrderQty;

    }

    public static String loadAutomatedSalesOrderNo() throws ScalarException
    {
        int count = 1;
        String newSalesOrderNumber = NewSalesOrderDAO.generateAutomatedSalesOrderNo();//generateAutomatedSalesOrderNo( count );


        return newSalesOrderNumber;
    }
    public static String loadAutomatedSampleSalesOrderNo() throws ScalarException
    {
        int count = 1;
        String newSalesOrderNumber = NewSalesOrderDAO.generateAutomatedSampleSalesOrderNo();//generateAutomatedSalesOrderNo( count );
        return newSalesOrderNumber;
    }

    /**
     * This method will increment and return the sales order number. The parameter specifies
     * the value by which the existing sales order number has to be incremented by, to get the
     * new sales order number. Normally this value is given as 1 as the new sales order number
     * is always one greater than the existing sales order number. This method is useful when
     * the incremented sales order number is already existing and we need the next incremented
     * sales order number, which can be achieved by passing the value as 2, 3, 4 and so on...
     *
     * @param incrementBy
     * @return salesOrderNumber
     * @throws ScalarException
     *
     * @deprecated we are not using this method since new pattern has been introduced.
     * @see NewSalesOrderDAO#generateAutomatedSalesOrderNo()
     */
    public static String generateAutomatedSalesOrderNo( int incrementBy ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int salesOrderId = -1;
        String salesOrderNo = null;
        String finalSalesOrderNo = null;
        Format formatter = null;
        String number = null;
        String salesOrderPrefix = null;
        int salesOrderLength = 0;

        try
        {
            conn  = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getSalesOrderInfo" ) );
            rs = pstmt.executeQuery();
            if( rs.next())
            {
                salesOrderPrefix = rs.getString("Sales_Order_Prefix");
                salesOrderLength = rs.getInt("Sales_Order_Length");
            }
            rs.close();
            rs = null;

            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.getAutomatedSalesOrderNo"));
            rs    = pstmt.executeQuery();

            if (rs.next())
            {
                salesOrderNo = rs.getString ("SalesOrder_No");
            }
            else
            {
                salesOrderNo = "0";
            }

            try
            {
                if(salesOrderNo != null)
                {
                    int indexValue = salesOrderNo.indexOf("-");
                    number = salesOrderNo.substring(indexValue+1);
                    String no = (Long.parseLong (number) + incrementBy )+"";
                    int noLength = no.length();
                    int totalLength = salesOrderLength - noLength ;
                    if( totalLength > 0 )
                    {
                        for(int  i = 0; i < totalLength ; i++  )
                        {
                            no = "0"+no;
                        }
                    }

                    if( salesOrderPrefix != null && salesOrderPrefix.trim().length() != 0 )
                    {
                        finalSalesOrderNo = salesOrderPrefix+"-"+no ;
                    }
                    else
                    {
                        finalSalesOrderNo = no;
                    }
                }
            }
            catch (NumberFormatException nfe)
            {
                finalSalesOrderNo = (rs.getInt ("SalesOrder_Id") + incrementBy )+"" ;
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Automated Sales Order No from database", sqlex);
        }

        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return finalSalesOrderNo;
    }

   public static HashMap loadOrderLineItemsHashMap(DBTransaction dbTransaction, int salesOrderId) throws ScalarException
   {
       HashMap map = new HashMap();
       ArrayList line_items = loadOrderLineItems(dbTransaction,salesOrderId);
       if(line_items != null)
       {
           Iterator list = line_items.iterator();
           while(list.hasNext())
           {
               LineItem item = (LineItem)list.next();
               map.put(new Integer(item.getLineItemId()),item);
           }
       }
       return map;
   }

   public static StringBuffer compareLineItems(LineItem oldItem, LineItem newItem) throws ScalarException
   {
       StringBuffer changes = new StringBuffer();

        if(oldItem.getQuantity() != newItem.getQuantity())
            changes.append("<br>Quantity changed from " + oldItem.getQuantity() + " to " + newItem.getQuantity() + " for line item " + newItem.getLineItemNo());
        if(!oldItem.getRequestDate().equals(newItem.getRequestDate()))
            changes.append("<br>Requested Ship date changed from " + oldItem.getRequestDate() + " to " + newItem.getRequestDate() + " for line item " + newItem.getLineItemNo());
        if(oldItem.getUnitPrice() != newItem.getUnitPrice())
            changes.append("<br>Unit price changed from " + oldItem.getUnitPrice() + " to " + newItem.getUnitPrice() + " for line item " + newItem.getLineItemNo());
       if(newItem.isCancel())
           changes.append("<br>LineItem " + newItem.getLineItemNo() + " has been cancelled");

       return changes;
   }

   public static String checkForAllLineItemChanges(DBTransaction dbTransaction, int salesOrderId, ArrayList lineItems,String no) throws ScalarException
   {
       HashMap map = loadOrderLineItemsHashMap( dbTransaction, salesOrderId );
       Iterator list = lineItems.iterator();
       StringBuffer allchanges = new StringBuffer();
       StringBuffer qtypricechanges = new StringBuffer();
       boolean oldTaxValue = false;
       String emptyStr = new String("\"empty\"");

        int changeNumber = Integer.parseInt(no);
        changeNumber =    changeNumber-1;

       while(list.hasNext())
       {

           LineItem newItem = (LineItem)list.next();
           LineItem oldItem = (LineItem)map.get(new Integer(newItem.getLineItemId()));
           boolean newTaxValue = newItem.getIsTaxable();
           try
           {
           oldTaxValue = oldItem.getIsTaxable();

            }catch(NullPointerException e){
                 oldTaxValue = false;
             }

           String taxValue = "";

           if( newTaxValue )
               taxValue = " Selected ";
           else
               taxValue = " Deselected ";

           if(oldItem != null)
           {
                if( oldItem.getPoLineItemNo() != newItem.getPoLineItemNo() )
                {
                    allchanges.append("<br> POLineItem is changed from <b><I> " +oldItem.getPoLineItemNo() + "</I></b>  to <b><I> " +newItem.getPoLineItemNo() + "</I></b>  for line item " + newItem.getLineItemNo());
                }
               if(! (oldItem.getManufacturingPartNo().trim()).equals(newItem.getManufacturingPartNo().trim()) )
               {
                    allchanges.append("<br>Mfg Part Number is changed from <b><I> " +("".equals(oldItem.getManufacturingPartNo().trim()) ? emptyStr : oldItem.getManufacturingPartNo() ) + "</I></b>  to <b><I> " +("".equals(newItem.getManufacturingPartNo().trim()) ? emptyStr : newItem.getManufacturingPartNo() ) + "</I></b>  for line item " + newItem.getLineItemNo());
               }
               if(! (oldItem.getCustomerPartNo().trim()).equals(newItem.getCustomerPartNo().trim()) )
               {
                  allchanges.append("<br> Customer Part Number is changed from <b><I> " +("".equals(oldItem.getCustomerPartNo().trim()) ? emptyStr : oldItem.getCustomerPartNo() ) + "</I></b>  to <b><I> " +("".equals(newItem.getCustomerPartNo().trim()) ? emptyStr : newItem.getCustomerPartNo() ) + "</I></b>  for line item " + newItem.getLineItemNo());
               }
               if(! (oldItem.getLineItemDescription().trim()).equals(newItem.getLineItemDescription().trim()) )
               {
                  allchanges.append("<br> LineItem Description is changed from <b><I> " +("".equals(oldItem.getLineItemDescription().trim()) ? emptyStr : oldItem.getLineItemDescription() ) + "</I></b>  to <b><I> " +("".equals(newItem.getLineItemDescription().trim()) ? emptyStr : newItem.getLineItemDescription() ) + "</I></b>  for line item " + newItem.getLineItemNo());
               }
                if( newTaxValue != oldTaxValue )
                {
                    allchanges.append("<br> Tax is <b><I> " +taxValue + "</I></b>  for line item " + newItem.getLineItemNo());
                }

                if(!oldItem.getRequestDate().equals(newItem.getRequestDate()))
                {
                    allchanges.append("<br>Requested Ship date is changed from <b><I>" + oldItem.getRequestDate() + "</I></b>  to <b><I>" + newItem.getRequestDate() + "</I></b> for line item " + newItem.getLineItemNo());
                }

                if( oldItem.isCancel() != newItem.isCancel())
                {
                    if( newItem.isCancel() == true )
                    {
                        allchanges.append("<br>LineItem " + newItem.getLineItemNo() + "  has been cancelled and it needs Approval");
                    }
                    else
                    {
                        allchanges.append("<br>LineItem " + newItem.getLineItemNo() + " status is moved from Cancel to Hold ");
                    }
                }

                 qtypricechanges.append("");

                 if(oldItem.getQuantity() != newItem.getQuantity())
                {
                    qtypricechanges.append("<br>Quantity is changed from <b><I>" + oldItem.getQuantity() + "</I></b>  to <b><I> " + newItem.getQuantity() + " </I></b>for line item " + newItem.getLineItemNo());
                }
                if( oldItem.getUnitPriceAsDouble() != newItem.getUnitPriceAsDouble())
                {
                    String oldPrice = StringUtils.formatAmountWithNoOfDecimals(oldItem.getUnitPriceAsDouble());
                    String newPrice = StringUtils.formatAmountWithNoOfDecimals(newItem.getUnitPriceAsDouble());

                    qtypricechanges.append("<br>Unit price is changed from <b><I>" + oldPrice + " </I></b> to <b><I>" + newPrice + "</I></b> for line item " + newItem.getLineItemNo());

                }

         }
         else
         {

             qtypricechanges.append("<br>LineItem " + newItem.getLineItemNo() + "  has been added");
         }

       }


            allchanges.append(qtypricechanges);

            String firstString = "";

            if( qtypricechanges.length() != 0 && qtypricechanges != null )
            {
                changeNumber = changeNumber+1;
                firstString = "Change Number is <b><I>  "+changeNumber+" </I></b>";
                allchanges.insert(0,firstString);
            }
            else
            {
                firstString = "";
                allchanges.insert(0,firstString);
            }

       return allchanges.toString();

   }

   //To show all details in Audittrail
    public static String checkSalesOrderChanges(DBTransaction dbTransaction, SalesOrder oldSalesOrder , SalesOrder newSalesOrder ,String no) throws ScalarException
    {
        StringBuffer salesorderchanges = new StringBuffer();
        StringBuffer taxchanges = new StringBuffer();
        String emptyStr = new String("\"empty\"");
        int changeNumber = 0;

            if(oldSalesOrder != null)
            {
                 salesorderchanges.append("");
                 taxchanges.append("");
                if( newSalesOrder.getReason() != null && newSalesOrder.getReason().trim().length() !=0 )
                {
                   salesorderchanges = salesorderchanges.append("<br> Reason is <b><I>" +newSalesOrder.getReason() + "</I></b>  " );
                }

                if( oldSalesOrder.getCustomerPO() != null )
                {
                    if(! oldSalesOrder.getCustomerPO().trim().equals((newSalesOrder.getCustomerPO()==null ? "" : newSalesOrder.getCustomerPO().trim())))
                    {
                        salesorderchanges = salesorderchanges.append("<br> Customer PO No is changed from <b><I> " +oldSalesOrder.getCustomerPO() + "</I></b>  to <b><I> " +newSalesOrder.getCustomerPO() + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getPoRev() != null )
                {
                    if(! oldSalesOrder.getPoRev().trim().equals((newSalesOrder.getPoRev()==null ? "" : newSalesOrder.getPoRev().trim())))
                    {
                        salesorderchanges = salesorderchanges.append("<br> PO Rev is changed from <b><I> " +( "".equals(oldSalesOrder.getPoRev().trim()) ? emptyStr : oldSalesOrder.getPoRev()) + "</I></b>  to <b><I> " +( "".equals(newSalesOrder.getPoRev().trim()) ? emptyStr : newSalesOrder.getPoRev())+ "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getPoDate() != null )
                {
                    if(! oldSalesOrder.getPoDate().trim().equals(newSalesOrder.getPoDate()==null ? "" : newSalesOrder.getPoDate().trim()))
                    {
                        salesorderchanges = salesorderchanges.append("<br> PO Date is changed from <b><I> " +( "".equals(oldSalesOrder.getPoDate().trim()) ? emptyStr : oldSalesOrder.getPoDate())+ "</I></b>  to <b><I> " +( "".equals(newSalesOrder.getPoDate().trim()) ? emptyStr : newSalesOrder.getPoDate()) + "</I></b>  " );
                    }
                }

                if( oldSalesOrder.getContactPerson() != null )
                {
                    if(! oldSalesOrder.getContactPerson().trim().equals(newSalesOrder.getContactPerson() == null ? "" : newSalesOrder.getContactPerson().trim()) )
                    {
                       salesorderchanges = salesorderchanges.append("<br> Contact Person is changed from <b><I> " +( "".equals(oldSalesOrder.getContactPerson().trim())? emptyStr : oldSalesOrder.getContactPerson() ) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getContactPerson().trim()) ? emptyStr : newSalesOrder.getContactPerson())  + "</I></b>  " );
                    }
                }
                if(oldSalesOrder.getPhoneNumber() != null )
                {
                    if(! oldSalesOrder.getPhoneNumber().trim().equals(newSalesOrder.getPhoneNumber() == null ? "" : newSalesOrder.getPhoneNumber().trim() ) )
                    {
                        salesorderchanges = salesorderchanges.append("<br> Phone is changed from <b><I> " + ( "".equals(oldSalesOrder.getPhoneNumber().trim()) ? emptyStr : oldSalesOrder.getPhoneNumber() )+ "</I></b>  to <b><I> " + ("".equals(newSalesOrder.getPhoneNumber().trim()) ? emptyStr : newSalesOrder.getPhoneNumber()) + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getFaxNumber() != null )
                {
                    if(! oldSalesOrder.getFaxNumber().trim().equals(newSalesOrder.getFaxNumber() == null ? "" : newSalesOrder.getFaxNumber().trim()) )
                    {
                        salesorderchanges = salesorderchanges.append("<br> Fax is changed from <b><I> " +( "".equals(oldSalesOrder.getFaxNumber().trim()) ? emptyStr : oldSalesOrder.getFaxNumber()) + "</I></b>  to <b><I> " +( "".equals(newSalesOrder.getFaxNumber().trim()) ? emptyStr : newSalesOrder.getFaxNumber()) + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getEmail() != null )
                {
                    if(! oldSalesOrder.getEmail().trim().equals(newSalesOrder.getEmail() == null ? "" : newSalesOrder.getEmail().trim()) )
                    {
                        salesorderchanges = salesorderchanges.append("<br> Email is changed from <b><I> " +( "".equals(oldSalesOrder.getEmail().trim()) ? emptyStr : oldSalesOrder.getEmail() ) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getEmail().trim()) ? emptyStr : newSalesOrder.getEmail() ) + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getSalesPerson() != null )
                {
                    if(! oldSalesOrder.getSalesPerson().trim().equals(newSalesOrder.getSalesPerson() == null ? "" : newSalesOrder.getSalesPerson().trim()) )
                    {
                        salesorderchanges = salesorderchanges.append("<br> Sales Person is changed from <b><I> " +( "".equals(oldSalesOrder.getSalesPerson().trim()) ? emptyStr : oldSalesOrder.getSalesPerson() ) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getSalesPerson().trim()) ? emptyStr : newSalesOrder.getSalesPerson()) + "</I></b>  " );
                    }
                }
                //To display Sales Rep Changes in Audit Trail.
                if( oldSalesOrder.getSalesRep() != null )
                {
                    if(! oldSalesOrder.getSalesRep().trim().equals(newSalesOrder.getSalesRep() == null ? "" : newSalesOrder.getSalesRep().trim() ) )
                    {
                        ClientInfo clientInfo = new ClientInfo();
                        clientInfo = UserDAO.getClientAddressInfo(  );
                        String salesRepresentative = null;
                        try
                        {
                            salesRepresentative = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.salesRep.Name" );
                            if ( salesRepresentative == null )
                            {
                                salesRepresentative = Labels.DEFAULT_REPRESENTATIVE;
                            }
                        }
                        catch ( NullPointerException e )
                        {
                            salesRepresentative    = Labels.DEFAULT_REPRESENTATIVE;
                        }
                        salesorderchanges = salesorderchanges.append("<br> " + salesRepresentative + " is changed from <b><I> " +( "".equals(oldSalesOrder.getSalesRep().trim()) ? emptyStr : oldSalesOrder.getSalesRep() ) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getSalesRep().trim()) ? emptyStr : newSalesOrder.getSalesRep() )+ "</I></b>  " );
                    }
                }
				//To audit correct shipping method
                if( oldSalesOrder.getShippingMethod() != null )
                {
                    String oldShippingMethod = oldSalesOrder.getShippingMethodName()+"   #   "+oldSalesOrder.getShippingAccount();

                    if(! oldShippingMethod.equals( newSalesOrder.getShippingMethodName()+"   #   "+newSalesOrder.getShippingAccount()) )
                    {
                        //System.out.println("old "+oldSalesOrder.getShippingMethod()+"   new    "+ newSalesOrder.getShippingMethodName()+"  olddd "+newSalesOrder.getShippingMethod());
                        String newShipMethod = newSalesOrder.getShippingMethodName();
                        if( ( newShipMethod == null || (newShipMethod.trim().length() == 0) ) && ( oldSalesOrder.getShippingMethod() == null || oldSalesOrder.getShippingMethod().trim().length() == 0 ) )
                        {
                            salesorderchanges = salesorderchanges.append("");
                        }
                        else
                        {
                            String shippingMethodLabel = null;
                            try
                            {
                                shippingMethodLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.salesOrder.ShippingMethod" );
                                if ( shippingMethodLabel == null )
                                {
                                    shippingMethodLabel = Labels.DEFAULT_SHIPPING_METHOD_LABEL;
                                }
                            }
                            catch ( NullPointerException e )
                            {
                                shippingMethodLabel    = Labels.DEFAULT_SHIPPING_METHOD_LABEL;
                            }
                            if(( newShipMethod == null || (newShipMethod.trim().length() == 0) ))
                            {
                                salesorderchanges = salesorderchanges.append("<br> "+shippingMethodLabel+" is changed from <b><I> " +oldSalesOrder.getShippingMethod()+"</I></b>  to <b><I> "+emptyStr+" </b></i> " );
                            }else
                            {
                                salesorderchanges = salesorderchanges.append("<br> "+shippingMethodLabel+" is changed from <b><I> " +("".equals(oldSalesOrder.getShippingMethod().trim()) ? emptyStr : oldSalesOrder.getShippingMethod()) + "</I></b>  to <b><I> " +newSalesOrder.getShippingMethodName()+"   #   "+newSalesOrder.getShippingAccount()+ "</I></b>  " );
                            }
                        }
                    }
                }
                if( oldSalesOrder.getShippingTerms() != null )
                {
                    if(! oldSalesOrder.getShippingTerms().trim().equals(newSalesOrder.getShippingTerms() == null ? "" : newSalesOrder.getShippingTerms().trim()) )
                    {
                        salesorderchanges = salesorderchanges.append("<br> Pay Terms is changed from <b><I> " +("".equals(oldSalesOrder.getShippingTerms().trim()) ? emptyStr : oldSalesOrder.getShippingTerms() ) + "</I></b>  to <b><I> " + ("".equals(newSalesOrder.getShippingTerms().trim()) ? emptyStr : newSalesOrder.getShippingTerms() ) + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getDeliveryTerms() != null )
                {
                    if(! oldSalesOrder.getDeliveryTerms().trim().equals(newSalesOrder.getDeliveryTerms() == null ? "" : newSalesOrder.getDeliveryTerms().trim()) )
                    {
                        String deliveryTermsLabel = null;
                        try
                        {
                            deliveryTermsLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.salesOrder.DeliveryTerms" );
                            if ( deliveryTermsLabel == null )
                            {
                                deliveryTermsLabel = Labels.DEFAULT_DELIVERY_TERMS_LABEL;
                            }
                        }
                        catch ( NullPointerException e )
                        {
                            deliveryTermsLabel    = Labels.DEFAULT_DELIVERY_TERMS_LABEL;
                        }
                        salesorderchanges = salesorderchanges.append("<br> "+deliveryTermsLabel+" is changed from <b><I> " +("".equals(oldSalesOrder.getDeliveryTerms().trim()) ? emptyStr : oldSalesOrder.getDeliveryTerms()) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getDeliveryTerms().trim()) ? emptyStr : newSalesOrder.getDeliveryTerms()) + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getRegionalRep() != null )
                {
                    if(! oldSalesOrder.getRegionalRep().trim().equals(newSalesOrder.getRegionalRep() == null ? "" : newSalesOrder.getRegionalRep().trim()) )
                    {
                        salesorderchanges = salesorderchanges.append("<br> Regional Rep is changed from <b><I> " +("".equals(oldSalesOrder.getRegionalRep().trim()) ? emptyStr : oldSalesOrder.getRegionalRep() ) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getRegionalRep().trim()) ? emptyStr : newSalesOrder.getRegionalRep()) + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getSalesOrderDate() != null )
                {
                    if(! oldSalesOrder.getSalesOrderDate().trim().equals(newSalesOrder.getSalesOrderDate() == null ? "" : newSalesOrder.getSalesOrderDate().trim()) )
                    {
                        salesorderchanges = salesorderchanges.append("<br> Booking Date is changed from <b><I> " +oldSalesOrder.getSalesOrderDate() + "</I></b>  to <b><I> " +newSalesOrder.getSalesOrderDate() + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getEndUser() != null )
                {
                    if(! oldSalesOrder.getEndUser().trim().equals(newSalesOrder.getEndUser() == null ? "" : newSalesOrder.getEndUser().trim()) )
                    {
                        salesorderchanges = salesorderchanges.append("<br> End User is changed from <b><I> " +("".equals(oldSalesOrder.getEndUser().trim()) ? emptyStr : oldSalesOrder.getEndUser()) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getEndUser().trim()) ? emptyStr : newSalesOrder.getEndUser())  + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getOdm() != null )
                {
                    if(! oldSalesOrder.getOdm().equals(newSalesOrder.getOdm()) )
                    {
                        String endUserLabel = null;
                        try
                        {
                            endUserLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.endCustomer" );
                            if ( endUserLabel == null )
                            {
                                endUserLabel = Labels.DEFAULT_END_CUSTOMER_LABEL;
                            }
                        }
                        catch ( NullPointerException e )
                        {
                            endUserLabel    = Labels.DEFAULT_END_CUSTOMER_LABEL;
                        }
                        salesorderchanges = salesorderchanges.append("<br> "+endUserLabel+" is changed from <b><I> " +("".equals(oldSalesOrder.getOdm().trim()) ? emptyStr : oldSalesOrder.getOdm()) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getOdm().trim()) ? emptyStr :newSalesOrder.getOdm()) + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getEndCustomer() != null )
                {
                    if(! oldSalesOrder.getEndCustomer().equals(newSalesOrder.getEndCustomer()) )
                    {
                        String endUserLabel = null;
                        try
                        {
                            endUserLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.endUser" );
                            if ( endUserLabel == null )
                            {
                                endUserLabel = Labels.DEFAULT_END_USER_LABEL;
                            }
                        }
                        catch ( NullPointerException e )
                        {
                            endUserLabel    = Labels.DEFAULT_END_USER_LABEL;
                        }
                        salesorderchanges = salesorderchanges.append("<br> "+endUserLabel+" is changed from <b><I> " +("".equals(oldSalesOrder.getEndCustomer().trim()) ? emptyStr : oldSalesOrder.getEndCustomer()) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getEndCustomer().trim()) ? emptyStr :newSalesOrder.getEndCustomer()) + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getBillingContactPerson() != null )
                {
                    if(! oldSalesOrder.getBillingContactPerson().trim().equals(newSalesOrder.getBillingContactPerson() == null ? "" : newSalesOrder.getBillingContactPerson().trim()) )
                    {
                       salesorderchanges = salesorderchanges.append("<br> Billing Contact Person is changed from <b><I> " +( "".equals(oldSalesOrder.getBillingContactPerson().trim())? emptyStr : oldSalesOrder.getBillingContactPerson() ) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getBillingContactPerson().trim()) ? emptyStr : newSalesOrder.getBillingContactPerson())  + "</I></b>  " );
                    }
                }
                if (oldSalesOrder.getBillingAddressId() != newSalesOrder.getBillingAddressId()) {
                    Address address = getAddress(dbTransaction, newSalesOrder.getBillingAddressId());
                    StringBuffer oldaddressStr = new StringBuffer();
                    StringBuffer addressStr = new StringBuffer();
                    if (address != null) {
                        if (address.getAddress1() != null) {
                            addressStr = addressStr.append(address.getAddress1());
                        }
                        if ((address.getAddress2() != null) && (address.getAddress2().trim().length() != 0)) {
                            addressStr = addressStr.append("\n" + address.getAddress2());
                        }
                        if ((address.getAddress3() != null) && (address.getAddress3().trim().length() != 0)) {
                            addressStr = addressStr.append("\n" + address.getAddress3());
                        }
                        if ((address.getAddress4() != null) && (address.getAddress4().trim().length() != 0)) {
                            addressStr = addressStr.append("\n" + address.getAddress4());
                        }
                        if ((address.getCity() != null) && (address.getCity().trim().length() != 0)) {
                            addressStr = addressStr.append("," + address.getCity());
                        }
                        if ((address.getState() != null) && (address.getState().trim().length() != 0)) {
                            addressStr = addressStr.append(", " + address.getState());
                        }
                        if ((address.getZipCode() != null) && (address.getZipCode().trim().length() != 0)) {
                            addressStr = addressStr.append(" , " + address.getZipCode());
                        }
                        if ((address.getCountry() != null) && (address.getCountry().trim().length() != 0)) {
                            addressStr = addressStr.append(" , " + address.getCountry());
                        }
                    }
                    Address oldaddress = getAddress(dbTransaction, oldSalesOrder.getBillingAddressId());

                    if (oldaddress != null) {
                        if (oldaddress.getAddress1() != null) {
                            oldaddressStr = oldaddressStr.append(oldaddress.getAddress1());
                        }
                        if ((oldaddress.getAddress2() != null) && (oldaddress.getAddress2().trim().length() != 0)) {
                            oldaddressStr = oldaddressStr.append("\n" + oldaddress.getAddress2());
                        }

                        if ((oldaddress.getAddress3() != null) && (oldaddress.getAddress3().trim().length() != 0)) {
                            oldaddressStr = oldaddressStr.append("\n" + oldaddress.getAddress3());
                        }
                        if ((oldaddress.getAddress4() != null) && (oldaddress.getAddress4().trim().length() != 0)) {
                            oldaddressStr = oldaddressStr.append("\n" + oldaddress.getAddress4());
                        }
                        if ((oldaddress.getCity() != null) && (oldaddress.getCity().trim().length() != 0)) {
                            oldaddressStr = oldaddressStr.append("," + oldaddress.getCity());
                        }
                        if ((oldaddress.getState() != null) && (oldaddress.getState().trim().length() != 0)) {
                            oldaddressStr = oldaddressStr.append(", " + oldaddress.getState());
                        }
                        if ((oldaddress.getZipCode() != null) && (oldaddress.getZipCode().trim().length() != 0)) {
                            oldaddressStr = oldaddressStr.append(" , " + oldaddress.getZipCode());
                        }
                        if ((oldaddress.getCountry() != null) && (oldaddress.getCountry().trim().length() != 0)) {
                            oldaddressStr = oldaddressStr.append(" , " + oldaddress.getCountry());
                        }
                    }
                    salesorderchanges = salesorderchanges.append("<br> BillingAddress is changed from <b><I>" + oldaddressStr + " </I></b> to <b><I> " + addressStr + "</I></b>  ");
                }
                if( oldSalesOrder.getShippingContactPerson() != null )
                {
                    if(! oldSalesOrder.getShippingContactPerson().trim().equals(newSalesOrder.getShippingContactPerson() == null ? "" : newSalesOrder.getShippingContactPerson().trim()) )
                    {
                       salesorderchanges = salesorderchanges.append("<br> Shipping Contact Person is changed from <b><I> " +( "".equals(oldSalesOrder.getShippingContactPerson().trim())? emptyStr : oldSalesOrder.getShippingContactPerson() ) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getShippingContactPerson().trim()) ? emptyStr : newSalesOrder.getShippingContactPerson())  + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getShippingAddressId() != newSalesOrder.getShippingAddressId())
                {
                      Address address = getAddress(dbTransaction, newSalesOrder.getShippingAddressId() );
                     StringBuffer oldaddressStr = new StringBuffer();
                     StringBuffer addressStr = new StringBuffer();
                      if(address != null)
                     {
                        newSalesOrder.setEmail( address.getEmail() );
                        newSalesOrder.setPhoneNumber( address.getPhoneNumber() );
                        newSalesOrder.setFaxNumber( address.getFaxNumber() );
                        if( address.getAddress1() != null )
                        {
                            addressStr = addressStr.append(address.getAddress1());
                        }
                        if( ( address.getAddress2() != null ) && ( address.getAddress2().trim().length() != 0 ) )
                        {
                            addressStr = addressStr.append( "\n" +address.getAddress2());
                        }
                         if( ( address.getAddress3() != null ) && ( address.getAddress3().trim().length() != 0 ) )
                        {
                            addressStr = addressStr.append("\n" +address.getAddress3());
                        }
                         if( ( address.getAddress4() != null ) && ( address.getAddress4().trim().length() != 0 ) )
                        {
                            addressStr = addressStr.append("\n" +address.getAddress4());
                        }
                        if( ( address.getCity() != null ) && ( address.getCity().trim().length() != 0 ) )
                        {
                            addressStr = addressStr.append( "," + address.getCity());
                        }
                        if( ( address.getState() != null ) && ( address.getState().trim().length() != 0 ) )
                        {
                            addressStr = addressStr.append( ", " + address.getState());
                        }
                        if( ( address.getZipCode() != null ) && ( address.getZipCode().trim().length() != 0 ) )
                        {
                            addressStr = addressStr.append(" , "  +address.getZipCode());
                        }
                        if( ( address.getCountry() != null ) && ( address.getCountry().trim().length() != 0 ) )
                        {
                            addressStr = addressStr.append( " , " + address.getCountry());
                        }
                      }
                        Address oldaddress = getAddress(dbTransaction, oldSalesOrder.getShippingAddressId() );

                    if(oldaddress != null)
                     {
                        oldSalesOrder.setEmail( oldaddress.getEmail() );
                        oldSalesOrder.setPhoneNumber( oldaddress.getPhoneNumber() );
                        oldSalesOrder.setFaxNumber( oldaddress.getFaxNumber() );

                        if( oldaddress.getAddress1() != null )
                        {
                            oldaddressStr = oldaddressStr.append(oldaddress.getAddress1());
                        }
                        if( ( oldaddress.getAddress2() != null ) && ( oldaddress.getAddress2().trim().length() != 0 ) )
                        {
                            oldaddressStr = oldaddressStr.append( "\n" +oldaddress.getAddress2());
                        }

                         if( ( oldaddress.getAddress3() != null ) && ( oldaddress.getAddress3().trim().length() != 0 ) )
                        {
                            oldaddressStr = oldaddressStr.append("\n" +oldaddress.getAddress3());
                        }
                         if( ( oldaddress.getAddress4() != null ) && ( oldaddress.getAddress4().trim().length() != 0 ) )
                        {
                            oldaddressStr = oldaddressStr.append("\n" +oldaddress.getAddress4());
                        }
                        if( ( oldaddress.getCity() != null ) && ( oldaddress.getCity().trim().length() != 0 ) )
                        {
                            oldaddressStr = oldaddressStr.append( "," + oldaddress.getCity());
                        }
                        if( ( oldaddress.getState() != null ) && ( oldaddress.getState().trim().length() != 0 ) )
                        {
                            oldaddressStr = oldaddressStr.append( ", " + oldaddress.getState());
                        }
                        if( ( oldaddress.getZipCode() != null ) && ( oldaddress.getZipCode().trim().length() != 0 ) )
                        {
                            oldaddressStr = oldaddressStr.append(" , "  +oldaddress.getZipCode());
                        }
                        if( ( oldaddress.getCountry() != null ) && ( oldaddress.getCountry().trim().length() != 0 ) )
                        {
                            oldaddressStr = oldaddressStr.append( " , " + oldaddress.getCountry());
                        }
                     }
                    salesorderchanges = salesorderchanges.append("<br> ShippingAddress is changed from <b><I>"+oldaddressStr+" </I></b> to <b><I> " +addressStr + "</I></b>  " );
               }

                if( oldSalesOrder.getShippingInstructions() != null )
                {
                    if(! oldSalesOrder.getShippingInstructions().trim().equals( newSalesOrder.getShippingInstructions() == null ? "" : newSalesOrder.getShippingInstructions().trim()) )
                    {
                        salesorderchanges = salesorderchanges.append("<br> ShippingInstructions are changed from <b><I> " + ("".equals(oldSalesOrder.getShippingInstructions().trim()) ? emptyStr : oldSalesOrder.getShippingInstructions()) + "</I></b>  to <b><I> " +("".equals(newSalesOrder.getShippingInstructions().trim()) ? emptyStr : newSalesOrder.getShippingInstructions() )  + "</I></b>  " );
                    }
                }

                if( oldSalesOrder.getFrieghtAsDouble() != newSalesOrder.getFrieghtAsDouble())
                {
                    salesorderchanges = salesorderchanges.append("<br> Frieght is changed from <b><I> " +oldSalesOrder.getFrieghtAsString() + "</I></b>  to <b><I> " +newSalesOrder.getFrieghtAsString() + "</I></b>  " );
                }
                if( oldSalesOrder.getFreightInstructions() != null )
                {
                    String newFreightInstructions = ( newSalesOrder.getFreightInstructions() == null ) ? "" : newSalesOrder.getFreightInstructions().trim();
                    if(! oldSalesOrder.getFreightInstructions().trim().equals( newFreightInstructions ) )
                    {
                        salesorderchanges = salesorderchanges.append("<br> Freight Instructions are changed from <b><I> " + ("".equals(oldSalesOrder.getFreightInstructions().trim()) ? emptyStr : oldSalesOrder.getFreightInstructions()) + "</I></b>  to <b><I> " +("".equals( newFreightInstructions ) ? emptyStr : newFreightInstructions )  + "</I></b>  " );
                    }
                }

                if( oldSalesOrder.getMiscName() != null )
                {
                    if(! oldSalesOrder.getMiscName().trim().equals(newSalesOrder.getMiscName() == null ? "" : newSalesOrder.getMiscName().trim() ) )
                    {
                        salesorderchanges = salesorderchanges.append("<br> Misc Name is changed from <b><I> " +("".equals(oldSalesOrder.getMiscName().trim()) ? emptyStr : oldSalesOrder.getMiscName()) + "</I></b>  to <b><I> " + ("".equals(newSalesOrder.getMiscName().trim()) ? emptyStr : newSalesOrder.getMiscName()) + "</I></b>  " );
                    }
                }
                if( oldSalesOrder.getMiscAsDouble() != newSalesOrder.getMiscAsDouble())
                {
                    salesorderchanges = salesorderchanges.append("<br> Misc is changed from <b><I> " +oldSalesOrder.getMiscAsString() + "</I></b>  to <b><I> " +newSalesOrder.getMiscAsString() + "</I></b>  " );
                }
                if( oldSalesOrder.getSalesTaxType() != null )
                {
                    if( !oldSalesOrder.getSalesTaxType().trim().equals( newSalesOrder.getSalesTaxType() == null ? "" : newSalesOrder.getSalesTaxType().trim()) )
                    {
                        float newtaxvalue = SalesOrderDAO.getTaxValue(null,newSalesOrder.getSalesTaxType());
                        taxchanges = taxchanges.append("<br> Sales Tax State is changed from <b><I> " +("0".equals(oldSalesOrder.getSalesTaxType().trim()) ? emptyStr : oldSalesOrder.getSalesTaxType()+"-"+ oldSalesOrder.getSalesTaxValue())+ "</I></b>  to <b><I> " +("0".equals(newSalesOrder.getSalesTaxType().trim()) ? emptyStr : newSalesOrder.getSalesTaxType() +"-"+ newtaxvalue )+ "</I></b>  " );
                    }
                }
                if(taxchanges != null)
                {
                    if( oldSalesOrder.getSubTotalAsDouble() != newSalesOrder.getSubTotalAsDouble() )
                    {
                        taxchanges = taxchanges.append("<br> SubTotal is changed from <b><I> " +oldSalesOrder.getSubTotalAsString() + "</I></b>  to <b><I> " +newSalesOrder.getSubTotalAsString() + "</I></b>  " );
                    }
                     if( oldSalesOrder.getSalesTaxAsDouble() != newSalesOrder.getSalesTaxAsDouble() )
                    {
                        taxchanges = taxchanges.append("<br> Sales Tax is changed from <b><I> " +oldSalesOrder.getSalesTaxAsString() + "</I></b>  to <b><I> " +newSalesOrder.getSalesTaxAsString() + "</I></b>  " );
                    }
                }
          }
          if(taxchanges != null)
          {
              salesorderchanges = salesorderchanges.append(taxchanges);
          }
         return salesorderchanges.toString();
    }

    //To get the Shipping Address corresponding to shipping address Id
    public static Address getAddress(DBTransaction dbTransaction , int addressId) throws ScalarException
      {
           Connection conn = null;
           PreparedStatement pstmt = null;
           ResultSet rs = null;
           Address address = null;
           boolean success = false;

           try {
               if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

               pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.getAddress"));

               pstmt.setInt (1, addressId);

               rs = pstmt.executeQuery();

               if (rs.next() == false) {
               }
               else {
                    address = new Address(rs.getInt("Address_ID"),
                                        rs.getString("Address_1"),
                                        rs.getString("Address_2"),
                                        rs.getString("Address_3"),
                                        rs.getString("Address_4"),
                                        rs.getString("City"),
                                        rs.getString("State"),
                                        rs.getString("ZipCode"),
                                        rs.getString("Country"),
                                        rs.getString("Phone_Number"),
                                        rs.getString("Fax_Number"),
                                        rs.getString("Email"),
                                        rs.getString("Contact_Person"),
                                        rs.getString("Cell_Number"));
               }

               success = true;
           }
           catch (Exception sqlex) {
               sqlex.printStackTrace();
               throw new ScalarException ("Could not get Address from database", sqlex);
           }
           finally {
                  if (dbTransaction == null) {
                      try {
                          if (conn != null) {
                              if (success == true) {
                                  conn.commit();
                              }
                              else {
                                  conn.rollback();
                              }
                          }
                      }
                      catch (SQLException ignore) {}
                  }

               if (rs != null) {
                   try { rs.close(); } catch (SQLException excp) {}
               }
               if (pstmt != null) {
                   try { pstmt.close(); } catch (SQLException excp) {}
               }
               if (conn != null) {
                   try { conn.close(); } catch (SQLException excp) {}
               }
           }

           return address;

      }




   public static void updateAddress(DBTransaction dbTransaction, int addressId, String phoneNumber, String faxNumber,String email) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.updateAddress"));
            pstmt.setString (1, phoneNumber);
            pstmt.setString (2, faxNumber);
            pstmt.setString (3, email);
            pstmt.setInt (4, addressId);
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update sales order details info into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

   }

    public static void updateAddress(DBTransaction dbTransaction, int salesOrderId, String phoneNumber, String faxNumber,String email, String contactPerson) throws ScalarException
	{
		  Connection conn = null;
		  PreparedStatement pstmt = null;
		  boolean success = false;



		  try
          {
			  // Get the connection from regular datasource if its not in a manual transaction
			  if (dbTransaction == null) {
				  conn = DataSource.getConnection();
			  }
			  else {
				  conn = dbTransaction.getConnection();
			  }

              pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.insertSalesOrderAddressDetails"));
              pstmt.setString (1, phoneNumber);
              pstmt.setString (2, faxNumber);
              pstmt.setString (3, email);
              pstmt.setInt (4, salesOrderId);
              pstmt.execute();

			  success = true;
		  }
		  catch (Exception sqle) {
			  sqle.printStackTrace();
			  throw new ScalarException ("Could not update sales order details info into database", sqle);
		  }
		  finally {
			  if (dbTransaction == null) {
				  try {
					  if (conn != null) {
						  if (success == true)
							  conn.commit();
						  else
							  conn.rollback();
					  }
				  }
				  catch (SQLException ignore) {}
			  }
			  if (pstmt != null) {
				  try { pstmt.close(); } catch (SQLException ignore){}
			  }
			  if (conn != null) {
				  try { conn.close(); } catch (SQLException ignore){}
			  }
		  }

	 }

   /*
   Added by Sarada to get the value of sales Tax corresponding to State
   */
    public static float getTaxValue (DBTransaction dbTransaction,String state) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        float taxvalue = 0.0f;
		try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.getTaxValue"));
            pstmt.setString(1,state);

            rs = pstmt.executeQuery();
            while(rs.next())
            {
                taxvalue = rs.getFloat("Tax");
            }

        }//try
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Tax  values from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
               try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
               try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
               try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return taxvalue;
    }

    public static boolean doesSalesTaxStateExist (DBTransaction dbTransaction,String state) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int totalstates = 0;
        boolean success = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.doesSalesTaxStateExist"));
            pstmt.setString (1, state);
            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                totalstates = rs.getInt("total");
            }
            if( totalstates > 0 )
            {
                return true;
            }

            return false;

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not find State, \"" + state + "\" in the database", sqle);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (rs != null)
            {
	  	        try { rs.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    public static int getSalesOrderIdbyNumber( String salesOrderNumber ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        int salesOrderId = -1;
        if( salesOrderNumber == null )
        {
            return salesOrderId;
        }

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getSalesOrderIdbyNumber" ) );
            pstmt.setString( 1, salesOrderNumber );

            rs = pstmt.executeQuery();

            if( rs.next() )
            {
                salesOrderId = rs.getInt( "SalesOrder_ID" );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get sales order ID for sales order number: " + salesOrderNumber, sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salesOrderId;
    }

    public static HashMap loadAllManfacturingPartNumbersWithComments() throws ScalarException
   {

       Iterator finishedGoodsList = FinishedGoodsDAO.loadAllFinishedGoods().iterator();
       HashMap mapComments = new HashMap();
       FinishedGoods finishedGoods = null;
       while(finishedGoodsList.hasNext())
       {
             finishedGoods = (FinishedGoods) finishedGoodsList.next();
             String mfgPartNo = finishedGoods.getManufacturingPartNo();
             String pComments = finishedGoods.getPComments();

             mapComments.put( mfgPartNo, pComments );
        }

       return mapComments;
   }

   public static HashMap loadAllManfacturingPartNumbersWithComments( int customerId, int isSample ) throws ScalarException
   {

       Iterator finishedGoodsList = NewSalesOrderDAO.loadAllFinishedGoodsWithOdmPartNos(customerId, 0, isSample ).iterator();
       HashMap mapComments = new HashMap();
       FinishedGoods finishedGoods = null;
       while(finishedGoodsList.hasNext())
       {
             finishedGoods = (FinishedGoods) finishedGoodsList.next();
             String mfgPartNo = finishedGoods.getManufacturingPartNo();
             String pComments = finishedGoods.getPComments();

             mapComments.put( mfgPartNo, pComments );
        }

       return mapComments;
   }


     private static String loadSearchString( String byDate ) throws ScalarException
     {
         String search_string = "";
         if( "ByRequestedDate".equalsIgnoreCase( byDate ) )
         {
             search_string = " and COOL_Requested_Date < CURRENT_TIMESTAMP";
         }
         else if( "ByScheduleDate".equalsIgnoreCase( byDate ) )
         {
             search_string = " and COOL_Schedule_Date < CURRENT_TIMESTAMP";
         }
         else
         {
            search_string = "";
         }
          return search_string ;
     }

    public static ArrayList loadAllLineItemsExceptShipped(DBTransaction dbTransaction,int salesOrderId, String byDate) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList lineItems = new ArrayList();

        if(salesOrderId == -1)
            return null;

        try
        {
            if (dbTransaction == null) {
                    conn = DataSource.getConnection();
            }
            else {
                    conn = dbTransaction.getConnection();
            }
            //Load all the open order lineItems
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAllLineItemsExceptShipped") + loadSearchString(byDate));
            pstmt.setInt (1, salesOrderId);

            rs = pstmt.executeQuery();
            LineItem lineItem = null;

            while(rs.next())
            {
                lineItem = new LineItem();

                lineItem.setSalesOrderId (salesOrderId);

                lineItem.setLineItemId(rs.getInt("LineItem_ID"));
                lineItem.setLineItemNo(rs.getInt("COOL_Line_Item_No"));
                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("COOL_Qty"));

                lineItem.setUnitPriceAsDouble(rs.getDouble("COOL_Unit_Cost"));
                lineItem.setAmountAsDouble(rs.getDouble("COOL_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COOL_Requested_Date"),null));
                lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_With_Substitutables"),null));
                lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_Without_Substitutables"),null));
                lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("COOL_Schedule_Date"),null));
                lineItem.setRevisedDate(StringUtils.formatDateToString(rs.getDate("COOL_Revised_Date"),null));
                lineItem.setStatus(rs.getString("COOL_Status"));
                lineItem.setIsTaxable((rs.getInt("COOL_Taxable") == 0) ? true : false);
                lineItem.setSalesOrderNo (rs.getString ("SalesOrder_No"));

                String suggDate = lineItem.getSuggScheduleDateWithoutSubstitutables();

                lineItem.setSuggScheduleDate (suggDate);

                String scheduleDate = lineItem.getScheduleDate();
                if ((scheduleDate == null) || (scheduleDate.trim().length() == 0)) {
                    lineItem.setScheduleDate (suggDate);
                }
                lineItem.setLineItemDescription(rs.getString("COOL_Description"));
                lineItem.setCustomerPartNo(rs.getString("Customer_PartNumber"));
                lineItem.setLabelNumber(rs.getString("Label_Number") );

                loadMarketingAndMfgPartNumbers(lineItem);

                loadHoldsForLineItem(lineItem);

                lineItems.add(lineItem);
            }

        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get present lineItems Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return lineItems;
    }

    public static String getLineItemDescriptionByMfgPartNumber( String mfgPartNumber ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String description = null;

        if( mfgPartNumber == null )
        {
            return description;
        }

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getLineItemDescriptionByMfgPartNumber" ) );
            pstmt.setString( 1, mfgPartNumber );

            rs = pstmt.executeQuery();

            if( rs.next() )
            {
                description = rs.getString( "Description" );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Description for MFG Part Number: " + mfgPartNumber, sqlex );
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return description;
    }

    //This method will return the Default BillingID of the customer
    public static int getDefaultBillingAddressId(int customerID) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int defaultBillingAddressId=0;

        if( customerID == 0 )
        {
            return -1;
        }
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getDefaultBillingAddressId" ) );
            pstmt.setInt( 1, customerID );

            rs = pstmt.executeQuery();

            if( rs.next() )
            {
                defaultBillingAddressId = rs.getInt( "C_DEFAULT_BILLING_ADDRESS_ID" );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get the Default Billing AddressID of the Customer: " + customerID, sqlex );
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return defaultBillingAddressId;
    }

    //This method will return the Default ShippingAddressID of the customer
    public static int getDefaultShippingAddressId(int customerID) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int defaultShippingAddressId=0;

        if( customerID == 0 )
        {
            return -1;
        }
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getDefaultShippingAddressId" ) );
            pstmt.setInt( 1, customerID );

            rs = pstmt.executeQuery();

            if( rs.next() )
            {
                defaultShippingAddressId = rs.getInt( "C_DEFAULT_SHIPPING_ADDRESS_ID" );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get the Default Shipping AddressID of the Customer: " + customerID, sqlex );
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return defaultShippingAddressId;
    }
    public static LineItem loadCancelledMarketingAndMfgPartNumbers(LineItem lineItem) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        if(lineItem == null)
            return null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadCancelledMarketingAndMfgPartNumbers"));

            pstmt.setInt (1, lineItem.getLineItemId());

            rs = pstmt.executeQuery();

            while(rs.next()) {

                lineItem.setManufacturingPartNo(rs.getString("Part_Number"));
                lineItem.setMarketingPartNo(rs.getString("P_Marketing_Part_Number"));
            }

        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get part number for this product", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return lineItem;
   }

    //This method will compare the line items of two sales orders and returns the change summary to be sent in the notifications
    public static String getNotificationChangeSummary(DBTransaction dbTransaction, int salesOrderId, ArrayList lineItems) throws ScalarException
    {
        HashMap map = loadOrderLineItemsHashMap(dbTransaction,salesOrderId);
        Iterator list = lineItems.iterator();
        StringBuffer changeSummary = new StringBuffer();
        boolean oldTaxValue = false;
        boolean status_changed = false;

        while(list.hasNext())
        {
            LineItem newItem = (LineItem)list.next();
            LineItem oldItem = (LineItem)map.get(new Integer(newItem.getLineItemId()));
            boolean newTaxValue = newItem.getIsTaxable();
            try
            {
                oldTaxValue = oldItem.getIsTaxable();
            }catch(NullPointerException e){
                 oldTaxValue = false;
            }

            String taxValue = "";
            String expediteValue="";

            if( newTaxValue )
                taxValue = " Selected ";
            else
                taxValue = " Deselected ";

            if( newItem.getIsExpedite() ) {
                expediteValue = " Selected ";
            }else {
                expediteValue = " Deselected ";
            }

            if(oldItem != null)
            {

                if( oldItem.getPoLineItemNo() != newItem.getPoLineItemNo() )
                    changeSummary.append("<br> PoLineItem is changed from <b><I> " +oldItem.getPoLineItemNo() + "</I></b>  to <b><I> " +newItem.getPoLineItemNo() + "</I></b>  for line item " + newItem.getLineItemNo());
                if( newTaxValue != oldTaxValue )
                    changeSummary.append("<br> Tax is <b><I> " +taxValue + "</I></b>  for line item " + newItem.getLineItemNo());
                if(oldItem.getQuantity() != newItem.getQuantity()) {
                    changeSummary.append("<br>Quantity is changed from <b><I>" + oldItem.getQuantity() + "</I></b>  to <b><I> " + newItem.getQuantity() + " </I></b>for line item " + newItem.getLineItemNo());
                    status_changed = true;
                }
                if(!oldItem.getRequestDate().equals(newItem.getRequestDate())) {
                    changeSummary.append("<br>Requested Ship date is changed from <b><I>" + oldItem.getRequestDate() + "</I></b>  to <b><I>" + newItem.getRequestDate() + "</I></b> for line item " + newItem.getLineItemNo());
                    status_changed = true;
                }

                if( oldItem.getUnitPriceAsDouble() != newItem.getUnitPriceAsDouble()) {
                    changeSummary.append("<br>Unit price is changed from <b><I>" + oldItem.getUnitPriceAsString() + " </I></b> to <b><I>" + newItem.getUnitPriceAsString() + "</I></b> for line item " + newItem.getLineItemNo());
                    status_changed = true;
                }
                if ( "APPROVED".equalsIgnoreCase( oldItem.getOldStatus() ) && status_changed ) {
                    changeSummary.append("<br>LineItem " + newItem.getLineItemNo() + " status is moved from Approved to Hold ");
                }
                if( status_changed )
                {
                    /* CHANGE THE STATUS TO TRUE WHEN QTY OR REQ-DATE OR PRICE ARE MODIFIED */
                    newItem.setLineItemDetailsModified( true );
                }
                if( newItem.isSplit() )
                {
                    changeSummary.append("<br>LineItem " + newItem.getLineItemNo() + " is split into two line items ");
                }
                if( oldItem.isCancel() != newItem.isCancel())
                {
                    if( newItem.isCancel() == true )
                    {
                        newItem.setLineItemIntialCancellation( true );
                        changeSummary.append("<br>LineItem " + newItem.getLineItemNo() + "  has been cancelled and it needs Approval");
                    }
                    else
                    {
                        changeSummary.append("<br>LineItem " + newItem.getLineItemNo() + " status is moved from Cancel to Hold ");
                    }
                }
                if( oldItem.getIsExpedite() != newItem.getIsExpedite() )
                    changeSummary.append("<br> Expedite Fee is <b><I> " +expediteValue + "</I></b>  for line item " + newItem.getLineItemNo());
                status_changed = false;
            }
            else
                changeSummary.append("<br>LineItem " + newItem.getLineItemNo() + "  has been added");
        }
        return changeSummary.toString();
   }

    public static boolean isSalesOrderApproved( int salesOrderId ) throws ScalarException
    {
        return isSalesOrderApproved( null, salesOrderId );
    }

    public static boolean isSalesOrderApproved( DBTransaction dbTransaction, int salesOrderId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean isApproved = false;

        try
        {
            if( dbTransaction == null )
            {
                conn  = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "sales.salesOrder.getPendingLineItemIDs" ) );
            pstmt.setInt( 1, salesOrderId );
	        rs = pstmt.executeQuery();

            if( rs.next() == false )
	        {
                isApproved = true;
	        }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get pending line item IDs for sales order id: " + salesOrderId, sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }

        return isApproved;
   }
    public static String[] loadEmailListForApprovedSalesOrderNotification()
    {
        String[] emailArray = null;
        try
        {
            emailArray = NotificationSetupDAO.loadEmailsByType( NOTIFICATION_EMAIL_TYPE_APPROVED_SALES_ORDERS );
        }
        catch( ScalarException se )
        {
            se.getRootCause().printStackTrace();
            System.out.println( "Exception while retrieving email list");
        }

        return emailArray;
    }

    public static String[] loadEmailListForSalesOrderNotification()
    {
        String[] emailArray = null;
        try
        {
            emailArray = NotificationSetupDAO.loadEmailsByType( NOTIFICATION_EMAIL_TYPE_SALES_ORDERS );
        }
        catch( ScalarException se )
        {
            se.getRootCause().printStackTrace();
            System.out.println( "Exception while retrieving email list");
        }

        return emailArray;
    }


    public static boolean isSalesOrderApprovedButFinanceReasonCode( int salesOrderId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean isApproved = false;

        try
        {
            conn  = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "sales.salesOrder.getLineItemsApprovedButFinanceReasonCode" ) );
            pstmt.setInt( 1, salesOrderId );
	        rs = pstmt.executeQuery();

            if( rs.next() == true )
	        {
                isApproved = true;
	        }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get pending line item IDs for sales order id: " + salesOrderId, sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }

        return isApproved;
   }

    //This method is used in Order Scheduling Search Screen to
    // find whether status of the sales order.
    public static String getLineItemStatusBySalesOrderId ( String salesOrderId ) throws ScalarException
    {

        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;

        String status = null;

        try
        {
            conn = DataSource.getConnection ();
            if ( salesOrderId != null )
            {
                cstmt = conn.prepareCall ( SqlQueryMappings.get ( "sales.salesOrder.getSalesOrderStatusForSearch" ) );
                cstmt.setString ( 1, salesOrderId );
                cstmt.registerOutParameter ( 2, java.sql.Types.VARCHAR );
            }

            cstmt.execute ();
            status = cstmt.getString ( 2 );
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not retrieve LineItem status for SalesOrder " + salesOrderId, e );
        }
        finally
        {
            if ( rset != null )
            {
                try {   rset.close ();  }   catch ( SQLException ignore )   {   }
            }
            if ( cstmt != null )
            {
                try {   cstmt.close (); }   catch ( SQLException ignore )   {   }
            }
            if ( conn != null )
            {
                try {   conn.close ();  }   catch ( SQLException ignore )   {   }
            }
        }

        return status;
    }

    public static int getLineItemShippedQty( int lineItemId )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int shippedQty = 0;

		if( lineItemId != -1 )
		{
			try
			{
				conn  = DataSource.getConnection();
				pstmt = conn.prepareStatement( SqlQueryMappings.get ( "sales.salesOrder.getLineItemShippedQty" ) );
				pstmt.setInt( 1, lineItemId );
				rs = pstmt.executeQuery();

				if( rs.next() == true )
				{
					shippedQty = rs.getInt( "SHIPPED_QTY" );
				}
			}
			catch (Throwable sqlex)
			{
				sqlex.printStackTrace();
				System.out.println ( "Could not get Shipped Qty for line item ID: " + lineItemId );
			}
			finally
			{
				if ( rs != null )
				{
					try
					{ rs.close(); } catch ( SQLException excp ) {}
				}
				if ( pstmt != null )
				{
					try { pstmt.close(); } catch ( SQLException excp ) {}
				}
				if ( conn != null )
				{
					try { conn.close(); } catch ( SQLException excp ) {}
				}
			}
		}

        return shippedQty;
	}

    //For splitting the Line Items
    public static boolean splitLineItems ( DBTransaction dbTransaction, ArrayList lineItems, String userId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        int lastLineItemFlag = 0;
        int firstLineItemFlag = 0;
        int maxLineItemNumber = -1;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "sales.salesorder.splitLineItems" ) );

            ArrayList items = new ArrayList ();
            for ( int i = 0; i < lineItems.size (); i++ )
            {
                LineItem lineItem = ( LineItem ) lineItems.get ( i );
                if ( ( lineItem.isSplit () == true ) && ( lineItem.getSplitQty () > 0 ) )
                {
                    items.add ( lineItem );
                }
            }
            Iterator list = items.iterator();
            int i=0;

            while(list.hasNext())
            {

                firstLineItemFlag = i;
                LineItem lineItem = ( LineItem ) list.next();

                //The new line item number is required
                //while creating the split line item in GP. Retreivin the new max line item number only once
                //for the first time. From the next time onwards the retrived line item number is being incremented and set
                // to the line item. This will the database iteration.
                if( maxLineItemNumber == -1 )
                {
                    maxLineItemNumber = SalesOrderDAO.loadMaxLineItemNo( dbTransaction, lineItem.getSalesOrderId() );
                }

                //setting the line item number and incrementing it for the next iteration
                lineItem.setNewLineItemNumber( maxLineItemNumber++ );

                if(list.hasNext())
                {
                    lastLineItemFlag = 0;
                }
                else
                {
                    lastLineItemFlag = 1;
                }
                cstmt.setInt    ( 1, lineItem.getSalesOrderId () );
                cstmt.setInt    ( 2, lineItem.getLineItemId () );
                cstmt.setInt    ( 3, lineItem.getSplitQty () );
                cstmt.setInt    ( 4, lastLineItemFlag );
                cstmt.setInt    ( 5, firstLineItemFlag );
                cstmt.setString ( 6, userId );
                cstmt.execute ();
                i++;
            }
            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not Split Line Items", sqle );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){}
            }
        }


        return success;
    }

    public static int loadCustomerId (int salesOrderId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int customerId = 0;

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.getCustomerId"));
            pstmt.setInt (1, salesOrderId);

            rs = pstmt.executeQuery();

            while(rs.next())
            {
                customerId = rs.getInt("Customer_ID");
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not get customerId", sqle );
        }
        finally
        {
           if ( rs != null )
            {
                try
                { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }
        return customerId;
    }

	//To insert BankInfo record in Bank_Table while creating SalesOrder itself
    public static void insertBankInfo ( DBTransaction dbTransaction, int salesorderId )throws ScalarException
    {
    	Connection conn = null;
    	PreparedStatement pstmt = null ;
    	ResultSet rs = null;
		ResultSet rs1 = null;
    	boolean success = false;

		try {

			 if (dbTransaction == null)
			 {
				conn = DataSource.getConnection();
			 }
			 else
			 {
				conn = dbTransaction.getConnection();
			 }
			 pstmt = conn.prepareStatement (SqlQueryMappings.get ("users.getBankId"));
			 rs = pstmt.executeQuery();
			 int bankid = 0;
			 if( rs.next() == true)
			 {
				bankid = rs.getInt("BankId");
			 }
			 pstmt.close();
			 pstmt = null;

			 if( bankid != 0 )
			 {
				pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.insertBankInfo"));
				pstmt.setInt( 1 , salesorderId );
				pstmt.setInt( 2 , bankid );
				pstmt.execute();
			 }
			 else
			 {
			 	 String emptyValue = "";
				 pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.salesOrder.insertEmptyValuesBankInfo"));
			     pstmt.setInt( 1 , salesorderId );
				 pstmt.setString ( 2, emptyValue );
				 pstmt.setString ( 3, emptyValue );
				 pstmt.setString ( 4, emptyValue );
				 pstmt.setString ( 5, emptyValue );
				 pstmt.setString ( 6, emptyValue );
				 pstmt.setString ( 7, emptyValue );
				 pstmt.setString ( 8, emptyValue );
				 pstmt.setString ( 9, emptyValue );
				 pstmt.setString ( 10, emptyValue );
				 pstmt.execute();
			 }
		     success = true;
		}catch( Exception sqle )
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not insert BankInfo into Database", sqle);
		}
		finally
		{
			if( dbTransaction == null)
			{
				try
				{	if( conn != null )
					{
						if( success == true)
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}catch(SQLException ignore){}
			}
			if( pstmt != null )
			{
				try{ pstmt.close(); }catch(SQLException ignore){}
			}
			if( conn != null )
			{
				try{ conn.close(); }catch(SQLException ignore){}
			}

		}

    }

	//To get phno, fax,emailfrom address table
	public static Address getCustomerAddressDetails ( DBTransaction dbTransaction, int addressId )throws ScalarException
	{
        Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    Address address = null;
	    boolean success = false;

        try
        {
		    conn = DataSource.getConnection();

		    pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.getAddress"));
		    pstmt.setInt (1, addressId);

		    rs = pstmt.executeQuery();

		    if (rs.next() == false)
		    {
			    // Address not found, ???Throw exception
		    }
		    else
		    {
                address = new Address();
                address.setPhoneNumber(rs.getString("Phone_Number"));
                address.setFaxNumber(rs.getString("Fax_Number"));
                address.setEmail(rs.getString("Email"));
		    }
		    success = true;
        }
	    catch (Exception sqlex)
        {
		    sqlex.printStackTrace();
		    throw new ScalarException ("Could not get Address from database", sqlex);
	    }
		finally
		{
			if( dbTransaction == null )
			{
				try
				{	if( conn != null )
					{
						if( success == true)
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}catch(SQLException ignore){}
			}
            if( rs != null )
			{
				try{ rs.close(); }catch(SQLException ignore){}
			}
            if( pstmt != null )
			{
				try{ pstmt.close(); }catch(SQLException ignore){}
			}
			if( conn != null )
			{
				try{ conn.close(); }catch(SQLException ignore){}
			}

		}//finally

		return address;
	}//method


    public static void  loadEndUserPartNumbersForLineItems ( DBTransaction dbTransaction, ArrayList lineItems, int endUserId)throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.loadEndUserPartNumber" ) );

            for( int i = 0; i < lineItems.size(); i++ )
            {
                LineItem lineItem = ( LineItem ) lineItems.get( i );
                pstmt.setInt( 1, endUserId );
                pstmt.setInt( 2, lineItem.getProductId() );

                rs = pstmt.executeQuery();
                if( rs.next() )
                {
                    lineItem.setCustomerPartNo( rs.getString("Customer_PartNumber") );
                    lineItem.setLabelNumber( rs.getString("Label_Number") );
                }
                else
                {
                    lineItem.setCustomerPartNo( "" );
                    lineItem.setLabelNumber( "" );
                }
            }
            success = true;
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get End User Part Numbers from database", sqlex );
        }
        finally
        {
            if( dbTransaction == null)
            {
                try
                {	if( conn != null )
                    {
                        if( success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }catch(SQLException ignore){}
            }
            if( rs != null )
            {
                try{ rs.close(); }catch(SQLException ignore){}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }

    }

    public static void loadCustomerPartNumbersForLineItems ( DBTransaction dbTransaction, ArrayList lineItems, int customerId, int odmId )throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
                if( odmId > 0 )
                {
            pstmt = conn.prepareStatement( "SELECT Customer_PartNumber FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = ?  AND ODM_ID = ? AND PRODUCT_ID = ? AND CUSTOMER_PARTNUMBER IN ( SELECT ISNULL(COOL_CUSTOMER_PARTNUMBER, '') FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE LINEITEM_ID = ? ) ");
                    pstmt.setInt( 1, customerId );
                    pstmt.setInt( 2, odmId );
                }
                else
                {
            pstmt = conn.prepareStatement( "SELECT Customer_PartNumber FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = ? AND PRODUCT_ID = ? AND ODM_ID IS NULL AND CUSTOMER_PARTNUMBER IN ( SELECT ISNULL(COOL_CUSTOMER_PARTNUMBER, '') FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE LINEITEM_ID = ? ) ");
                    pstmt.setInt( 1, customerId );
                }
                for( int i = 0; i < lineItems.size(); i++ )
                {
                    LineItem lineItem = (LineItem) lineItems.get(i);
              if( odmId > 0 ){
                  pstmt.setInt( 3, lineItem.getProductId() );
                  pstmt.setInt( 4, lineItem.getLineItemId() );
              }else{
                  pstmt.setInt( 2, lineItem.getProductId() );
                  pstmt.setInt( 3, lineItem.getLineItemId() );
              }

              rs = pstmt.executeQuery();
              if(rs.next())
              {
                  lineItem.setCustomerPartNo(rs.getString("Customer_PartNumber"));
              }
              else
              {
                  lineItem.setCustomerPartNo("");
              }
          }

          success = true;
      }
      catch ( Exception sqlex )
      {
          sqlex.printStackTrace ();
          throw new ScalarException ( "Could not get Customer Part Numbers from database", sqlex );
      }
        finally
        {
            if( dbTransaction == null)
            {
                try
                {	if( conn != null )
                    {
                        if( success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }catch(SQLException ignore){}
            }
            if( rs != null )
            {
                try{ rs.close(); }catch(SQLException ignore){}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }

    }

    // Returns list of years for displaying in search pages. Starting year and Ending year are defined in Constants.
    public static ArrayList getYearList ()
    {
        int start_year = Constants.YEAR_START;
        int end_year = Constants.YEAR_END;

        ArrayList yearList = new ArrayList ();
        for ( int year = start_year; year <= end_year; year++ )
        {
            yearList.add ( new LabelValueBean ( year + "", year + "" ) );
        }
        return yearList;
    }

    public static void loadClientAddress(SalesOrder salesOrder)  throws ScalarException
    {
        String clientaddressId =  UserDAO.getClientAddressId();
        String addressId = null;
        String clientname = null;

        int finalAddressId = 0;
        if(clientaddressId != null)
        {
            StringTokenizer st = new StringTokenizer(clientaddressId,"@");
            try
            {
                addressId = st.nextToken();
                clientname = st.nextToken();
            }catch(NoSuchElementException e)
            {
                 clientname  = "";
            }
        }
        if(addressId  != null)
        {
            finalAddressId = Integer.parseInt(addressId);
        }
        Address clientAddress= UserDAO.loadClientAddressDetails( null , finalAddressId );

        salesOrder.setClientAddressAsObject(clientAddress);
        String clientAddress1 = SalesOrder.getClientAddressAsString(clientAddress);
        salesOrder.setClientAddress(clientAddress1);

    }

    /**
     * Returns the sales order number associated with the sales order id passed as the argument
     *
     * @param dbTransaction
     * @param salesOrderId
     * @return String
     * @throws ScalarException
     */
    public static String getSalesOrderNumber( DBTransaction dbTransaction,  int salesOrderId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String salesOrderNumber = "";

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }
            pstmt = conn.prepareStatement( "select SalesOrder_No from Sales_Order where SalesOrder_Id = ?" );
            pstmt.setInt( 1, salesOrderId );

            rs = pstmt.executeQuery();

            while(rs.next())
            {
                salesOrderNumber = rs.getString( "SalesOrder_No" );
            }

        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not get sales order number pertaining to the sales order id " + salesOrderId, sqle );
        }
        finally
        {
           if ( rs != null )
            {
                try
                { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }

        return salesOrderNumber;
    }

    /**
     * Returns the sales order number associated with the Line Item ID passed as the argument
     *
     * @param dbTransaction
     * @param lineItemID
     * @return String
     * @throws ScalarException
     */
    public static String getSalesOrderNumberByLineItemID( DBTransaction dbTransaction,  int lineItemID ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String salesOrderNumber = "";

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement( "select SalesOrder_No from SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL WHERE SO.SalesOrder_ID = COOL.SalesOrder_ID and COOL.LineItem_ID = ?" );
            pstmt.setInt( 1, lineItemID );

            rs = pstmt.executeQuery();

            while(rs.next())
            {
                salesOrderNumber = rs.getString( "SalesOrder_No" );
            }

        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not get sales order number pertaining to the line item id " + lineItemID, sqle );
        }
        finally
        {
           if ( rs != null )
            {
                try
                { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }

        return salesOrderNumber;
    }

    //To update Booking Dates in COOL & CCOl tables when Booking Date is modified in New Sales Order Screen.
    public static void updateLineItemBookingDates ( DBTransaction dbTransaction, SalesOrder salesOrder ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

      try
      {
          if ( dbTransaction == null )
          {
              conn = DataSource.getConnection ();
          }
          else
          {
              conn = dbTransaction.getConnection ();
          }
          pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.salesOrder.updateCOOLBookingDates" ) );
          pstmt.setDate ( 1, StringUtils.getSQLDate ( salesOrder.getSalesOrderDate () ) );
          pstmt.setInt ( 2, salesOrder.getSalesOrderId () );

          pstmt.executeUpdate ();
          pstmt.close ();
          pstmt = null;

          pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.salesOrder.updateCCOBookingDates" ) );
          pstmt.setDate ( 1, StringUtils.getSQLDate ( salesOrder.getSalesOrderDate () ) );
          pstmt.setInt ( 2, salesOrder.getSalesOrderId () );
          pstmt.executeUpdate ();

          success = true;
      }
      catch ( Exception sqlex )
      {
          sqlex.printStackTrace ();
          throw new ScalarException ( "Could not save Booking date in to database", sqlex );
      }
      finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }

    }

    /**
     * This method will return the sales tax type of the given sales order
     *
     * @param dbTransaction
     * @param salesOrderID
     * @return
     * @throws ScalarException
     */
    public static String getSalesOrderTaxType( DBTransaction dbTransaction, int salesOrderID ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        String salesTaxType     = "";

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement ( "select SO_Sales_Tax_Type from Sales_Order where SalesOrder_ID = ?" );
            pstmt.setInt ( 1, salesOrderID );
            rset = pstmt.executeQuery();

            while( rset.next() )
            {
                salesTaxType = rset.getString( "SO_Sales_Tax_Type" );
            }

        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Error while retreiving the Sales Tax Type of the given sales order", sqlex );
        }
        finally
        {
            if( rset != null )
            {
                try{ rset.close(); }catch(SQLException ignore){}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }//finally

        return salesTaxType;
    }

    /**
     * This method removes the specified holds for all the line items pertaining to the given sales order ID
     *
     * @param dbTransaction
     * @param salesOrderID
     * @param removeOperationsHold
     * @param removeMarkettingHold
     * @param removeSalesHold
     * @param removeFinanceHold
     * @throws ScalarException
     */
    public static void removeSalesOrderHolds( DBTransaction dbTransaction, int salesOrderID, boolean removeOperationsHold, boolean removeMarkettingHold, boolean removeSalesHold, boolean removeFinanceHold ) throws ScalarException
    {

        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            cstmt = conn.prepareCall ( "{ call sp_ct_remove_sales_order_holds( ?, ?, ?, ?, ? ) }" );
            cstmt.setInt( 1, salesOrderID );
            cstmt.setInt( 2, removeOperationsHold ? 1 : 0 );
            cstmt.setInt( 3, removeMarkettingHold ? 1 : 0 );
            cstmt.setInt( 4, removeSalesHold      ? 1 : 0 );
            cstmt.setInt( 5, removeFinanceHold    ? 1 : 0 );

            cstmt.executeUpdate();

            success = true;
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Exception while removing the holds on the sales order", sqlex );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success == true )
                    {
                        try
                        {
                            conn.commit ();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException ( "Exception while committing the remove sales order hold", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException ( "Exception while rolling back the remove sales order hold", e );
                        }
                    }
                }
            }
            if( cstmt != null )
            {
                try{ cstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }
        }//finally
    }
    /**
     * Retreives and sets the line item ID for the line items given in the list.
     *
     * @param dbTransaction
     * @param lineItemList
     * @param salesOrderID
     * @throws ScalarException
     */
    public static void setLineItemIds( DBTransaction dbTransaction, ArrayList lineItemList, int salesOrderID ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        LineItem lineItem       = null;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            cstmt = conn.prepareCall( "{ call sp_ct_sls_get_line_item_id( ?, ?, ? ) }" );

            for( int i = 0; i < lineItemList.size(); i++ )
            {
                lineItem = ( LineItem )lineItemList.get( i );
                cstmt.setInt( 1, salesOrderID );
                cstmt.setInt( 2, lineItem.getLineItemNo() );
                cstmt.registerOutParameter( 3, Types.INTEGER );
                cstmt.execute();
                lineItem.setLineItemId( cstmt.getInt( 3 ) );
            }


        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Exception while retreiving the line item ID", sqlex );
        }
        finally
        {
            if( cstmt != null )
            {
                try{ cstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }//finally
    }

    // To load the Order type( Sales Order or Sample Order)
    public static String getOrderType( DBTransaction dbTransaction, int salesOrderID ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        String orderType     = "";

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement ( "select SO_Is_Sample from Sales_Order where SalesOrder_ID = ?" );
            pstmt.setInt ( 1, salesOrderID );
            rset = pstmt.executeQuery();

            while( rset.next() )
            {
                orderType = rset.getString( "SO_Is_Sample" );
            }

        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Error while retreiving the Order Type of the given salesOrderId", sqlex );
        }
        finally
        {
            if( rset != null )
            {
                try{ rset.close(); }catch(SQLException ignore){}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }//finally

        return orderType;
    }
    /**
    * To load the status of Sales order.If the status of any Line Item is Shipped then it returns Status as Shipped.
    * @param dbTransaction , salesOrderId
    * @return status
    */

    public static String loadSalesOrderStatus( DBTransaction dbTransaction, String salesOrderId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        String status           = "";

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement ( "select CSLIT_Status from Customer_open_order_lineItem COOL,Customer_Shipping_line_item_table CSLIT where COOL.LineItem_ID = CSLIT.LineItem_ID and CSLIT_Status = 'SHIPPED' and COOL.SalesOrder_Id = ?" );
            pstmt.setInt ( 1, Integer.parseInt(salesOrderId) );
            rset = pstmt.executeQuery() ;
            while( rset.next() )
            {
                String tempStatus = rset.getString("CSLIT_Status");
                if( tempStatus.equalsIgnoreCase("SHIPPED") )    //if any one of the line item status is shipped then change the
                {                                               //status of Sales Order to SHIPPED.
                    status = "SHIPPED";
                    break;
                }
            }

        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Error while retreiving the Status of Sales Order", sqlex );
        }
        finally
        {
            if( rset != null )
            {
                try{ rset.close(); }catch(SQLException ignore){}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }//finally

        return status;

    }

   //To load the Customer Label Numbers
   public static HashMap loadManfacturingPartNumbersWithLabelNumbers(DBTransaction dbTransaction, int customerId) throws ScalarException
   {

       Iterator finishedGoodsList = loadOnlyFinishedGoodsHavingCustomerPartNos(customerId).iterator();
       HashMap mapLabelNos = new HashMap();
       FinishedGoods finishedGoods = null;
       while(finishedGoodsList.hasNext())
       {
             finishedGoods = (FinishedGoods) finishedGoodsList.next();
             String mfgPartNo = finishedGoods.getManufacturingPartNo();
             String labelNos = finishedGoods.getLabelNumber() ;
             mapLabelNos.put( mfgPartNo, labelNos );
       }
       return mapLabelNos;
   }

	public static HashMap loadOrderLineItemsMapWithCancelledLineItems(int salesOrderId) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		HashMap lineItems = new HashMap();

		if(salesOrderId == -1)
			return null;

		try
		{
			conn = DataSource.getConnection();

			pstmt = conn.prepareStatement ("SELECT * FROM fn_load_all_line_items_for_sw_commercial_invoice( ? ) order by Line_Item_No");

			pstmt.setInt (1, salesOrderId);

			rs = pstmt.executeQuery();
			LineItem lineItem = null;
			int lineItemId = -1;

			while(rs.next())
			{
				lineItem = new LineItem();

				lineItemId = rs.getInt("LineItem_ID");
				lineItem.setSalesOrderId (salesOrderId);
				lineItem.setLineItemId(lineItemId);
				lineItem.setLineItemNo(rs.getInt("Line_Item_No"));

				lineItem.setPoLineItemNo(rs.getInt("PO_Line_Item_No"));

				lineItem.setProductId(rs.getInt("Product_ID"));
				lineItem.setQuantity(rs.getInt("Qty"));

                lineItem.setUnitPriceAsDouble(rs.getDouble("Unit_Cost"));
				lineItem.setAmount(rs.getFloat("Amount"));
				lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("Requested_Date"),null));
				lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("Sugg_Schedule_Date_With_Substitutables"),null));
				lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("Sugg_Schedule_Date_Without_Substitutables"),null));
				lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("Schedule_Date"),null));
				lineItem.setRevisedDate(StringUtils.formatDateToString(rs.getDate("Revised_Date"),null));
				lineItem.setStatus(rs.getString("Status"));
				lineItem.setIsTaxable((rs.getInt("Taxable") == 0) ? true : false);
				lineItem.setSalesOrderNo (rs.getString ("SalesOrder_No"));

				loadMarketingAndMfgPartNumbers(lineItem);
				lineItems.put(lineItemId+"", lineItem);
			}
		}
		catch (Exception sqlex) {
			sqlex.printStackTrace();
			throw new ScalarException ("Could not get lineItems Info from database", sqlex);
		}
		finally {
			if (rs != null) {
				try { rs.close(); } catch (SQLException excp) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException excp) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException excp) {}
			}
		}

		return lineItems;
    }

    public static String getAddressWithoutContactPersonAsString( Address address )
    {
        StringBuffer tempAddress =   new StringBuffer("");
        if ( address == null )
        {
            return "";
        }
        String address1 = address.getAddress1();
        String address2 = address.getAddress2();
        String address3 = address.getAddress3();
        String address4 = address.getAddress4();
        String city = address.getCity();
        String zipCode= address.getZipCode();
        String state = address.getState();
        String country = address.getCountry();

        if(address1 != null && !( "".equals ( address1.trim() ) ) )
        {
            tempAddress.append(address1);
        }
        if(address2!=null && !( "".equals ( address2.trim() ) ) )
        {
            tempAddress.append(address2);
        }
        if(address3!=null && !( "".equals ( address3.trim() ) ) )
        {
           tempAddress.append(address3);
        }
        if(address4!=null && !( "".equals ( address4.trim() ) ) )
        {
           tempAddress.append(address4);
        }
        if(city!=null && !( "".equals ( city.trim() ) ) )
        {
            tempAddress.append(city);
        }
        if(state!=null && !( "".equals ( state.trim() ) ) )
        {
            tempAddress.append(state);
        }
        if(zipCode!=null && !( "".equals ( zipCode.trim() ) ) )
        {
            tempAddress.append(zipCode);
        }
        if(country!=null && !( "".equals ( country.trim() ) ) )
        {
            tempAddress.append(country);
        }
        return tempAddress.toString();
    }

    public static String setCustomerHolds( DBTransaction dbTransaction, User user, ArrayList lineItemList, int customerId, int salesOrderId, boolean isSample ) throws ScalarException
    {
        LineItem lineItem       = null;
        int count               = lineItemList.size();
        int newLineItemsCount   = 0;
        int changeLineItemsCount   = 0;
        Customer tempCustomer   = null;
        StringBuffer sb         = new StringBuffer();
        boolean isChiptracUser  = true;
        boolean disableLineItem = false;
        String markertingHold   = "";
        String financeHold      = "";
        String operationHold    = "";
        String salesHold        = "";


        if( user.getUserType() !=  Constants.USER_TYPE_CHIPTRAC )
        {
            isChiptracUser = false;
        }

        tempCustomer = CustomerDAO.loadCustomerHolds( dbTransaction, customerId, isSample );

        try
        {
            sb.append( " with following customer holds <br><table border='0' width='75%'><tr><td width='40%' align='center'>&nbsp;</td><td class='NArialL' width='15%' align='center'>Mktg</td><td  width='15%' class='NArialL'  align='center'>Fin</td><td  width='15%' class='NArialL' align='center'>Ops</td><td width='15%' class='NArialL' align='center'>Sales</td></tr>" );
            for( int i = 0; i < count; i++ )
            {
                lineItem = ( LineItem ) lineItemList.get( i );

                if ( !isChiptracUser && "APPROVED".equalsIgnoreCase( lineItem.getStatus() ) )
                {
                    disableLineItem = true;
                }
                else
                {
                    disableLineItem = false;
                }

                //System.out.print("[ Cust-ID =" + customerId + "][ SO-ID =" + salesOrderId + "][ LI-ID =" + lineItem.getLineItemId() + "][ LI No =" + lineItem.getLineItemNo()  +  "][ LI Status =" + lineItem.getStatus() +  "][ LI F-Status =" + lineItem.getFinalStatus() +  "][ LI Modify =" + lineItem.isLineItemDetailsModified()+  "][ isCancel =" + lineItem.isCancel() + "][ IsInitalCancel =" + lineItem.isLineItemIntialCancellation() );
                //System.out.println("][ isSplit = " + lineItem.isSplit() + "][ isLineItemSplited =" + lineItem.isLineItemSplited() + "][ isTransfer =" + lineItem.getIsTransfer() + "\n"  );

                if( lineItem.getLineItemId() != -1 && !lineItem.getIsTransfer() && lineItem.isLineItemDetailsModified() && ! ( "SHIPPED".equalsIgnoreCase( lineItem.getStatus() )  || "PARTIALLY SHIPPED".equalsIgnoreCase( lineItem.getFinalStatus() ) || "ISSUED".equalsIgnoreCase( lineItem.getFinalStatus() ) || disableLineItem ) )
                {
                    String scheduleDate= null;
                    //lineItem.setLineItemId( isLineItemExists( dbTransaction, salesOrderId, lineItem ) );
                    sb.append( "<tr><td class='NArialL'> Line Item No: " + lineItem.getLineItemNo() + " </td>");
                    sb.append( "<td class='NArialS' align='center'> " + ( tempCustomer.getMarketingHold().trim().length() == 0 ? "APPROVED" : ( tempCustomer.getMarketingHold().equalsIgnoreCase( "0" ) ? "X": tempCustomer.getMarketingHold() ) ) + " </td>" );
                    sb.append( "<td class='NArialS' align='center'> " + ( tempCustomer.getFinanceHold().trim().length() == 0 ? "APPROVED" : ( tempCustomer.getFinanceHold().equalsIgnoreCase( "0" ) ? "X": tempCustomer.getFinanceHold() ) ) + " </td>" );
                    sb.append( "<td class='NArialS' align='center'> " + ( tempCustomer.getOperationsHold().trim().length() == 0 ? "APPROVED" : ( tempCustomer.getOperationsHold().equalsIgnoreCase( "0" ) ? "X": tempCustomer.getOperationsHold() ) ) + " </td>" );
                    sb.append( "<td class='NArialS' align='center'> " + ( tempCustomer.getSalesHold().trim().length() == 0 ? "APPROVED" : ( tempCustomer.getSalesHold().equalsIgnoreCase( "0" ) ? "X": tempCustomer.getSalesHold() ) ) + " </td></tr>" );

                    if( lineItem.getScheduleDate() == null || "".equalsIgnoreCase( lineItem.getScheduleDate() ) || "null".equalsIgnoreCase( lineItem.getScheduleDate() ) )
                    {
                        scheduleDate= lineItem.getRequestDate();
                    }
                    else
                    {
                        scheduleDate= lineItem.getScheduleDate();
                    }                   

                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getMarketingHold(), ( tempCustomer.getMarketingHold().trim().length() == 0 ? 0 : 1 ), "MARKETING", user.getUserId(), lineItem.getLineItemId(), salesOrderId, lineItem.getScheduleDate() );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getFinanceHold(), ( tempCustomer.getFinanceHold().trim().length() == 0 ? 0 : 1 ), "FINANCE", user.getUserId(), lineItem.getLineItemId(), salesOrderId, lineItem.getScheduleDate() );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getSalesHold(), ( tempCustomer.getSalesHold().trim().length() == 0 ? 0 : 1 ), "SALES", user.getUserId(), lineItem.getLineItemId(), salesOrderId, lineItem.getScheduleDate() );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getOperationsHold(), ( tempCustomer.getOperationsHold().trim().length() == 0 ? 0 : 1 ), "OPERATIONS", user.getUserId(), lineItem.getLineItemId(),  salesOrderId, ( tempCustomer.getOperationsHold().trim().length() == 0 ? scheduleDate : lineItem.getScheduleDate() )  );

                    changeLineItemsCount++;
                }
                else if( lineItem.getLineItemId() != -1 && !lineItem.getIsTransfer() && lineItem.isCancel() && "Cancellation needs approval".equalsIgnoreCase( lineItem.getStatus() ) && lineItem.isLineItemIntialCancellation() )
                {
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getMarketingHold(), ( tempCustomer.getMarketingHold().trim().length() == 0 ? 0 : 1 ), "MARKETING", user.getUserId(), lineItem.getLineItemId(), salesOrderId, null );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getFinanceHold(), ( tempCustomer.getFinanceHold().trim().length() == 0 ? 0 : 1 ), "FINANCE", user.getUserId(), lineItem.getLineItemId(), salesOrderId, null );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getSalesHold(), ( tempCustomer.getSalesHold().trim().length() == 0 ? 0 : 1 ), "SALES", user.getUserId(), lineItem.getLineItemId(), salesOrderId, null );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getOperationsHold(), ( tempCustomer.getOperationsHold().trim().length() == 0 ? 0 : 1 ), "OPERATIONS", user.getUserId(), lineItem.getLineItemId(),  salesOrderId, null );
                    changeLineItemsCount++;
                }
                else if( lineItem.getLineItemId() != -1 && "HOLD".equalsIgnoreCase( lineItem.getStatus() ) && lineItem.isLineItemSplited() )
                {
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getMarketingHold(), ( tempCustomer.getMarketingHold().trim().length() == 0 ? 0 : 1 ), "MARKETING", user.getUserId(), lineItem.getLineItemId(), salesOrderId, null );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getFinanceHold(), ( tempCustomer.getFinanceHold().trim().length() == 0 ? 0 : 1 ), "FINANCE", user.getUserId(), lineItem.getLineItemId(), salesOrderId, null );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getSalesHold(), ( tempCustomer.getSalesHold().trim().length() == 0 ? 0 : 1 ), "SALES", user.getUserId(), lineItem.getLineItemId(), salesOrderId, null );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getOperationsHold(), ( tempCustomer.getOperationsHold().trim().length() == 0 ? 0 : 1 ), "OPERATIONS", user.getUserId(), lineItem.getLineItemId(),  salesOrderId, ( tempCustomer.getOperationsHold().trim().length() == 0 ? lineItem.getRequestDate() : null )  );
                    changeLineItemsCount++;
                }
                if( lineItem.getLineItemId() == -1 || lineItem.getIsTransfer() )
                {
                    lineItem.setLineItemId( isLineItemExists( dbTransaction, salesOrderId, lineItem ) );
                    sb.append( "<tr><td class='NArialL'> Line Item No: " + lineItem.getLineItemNo() + " </td>");
                    sb.append( "<td class='NArialS' align='center'> " + ( tempCustomer.getMarketingHold().trim().length() == 0 ? "APPROVED" : ( tempCustomer.getMarketingHold().equalsIgnoreCase( "0" ) ? "X": tempCustomer.getMarketingHold() ) ) + " </td>" );
                    sb.append( "<td class='NArialS' align='center'> " + ( tempCustomer.getFinanceHold().trim().length() == 0 ? "APPROVED" : ( tempCustomer.getFinanceHold().equalsIgnoreCase( "0" ) ? "X": tempCustomer.getFinanceHold() ) ) + " </td>" );
                    sb.append( "<td class='NArialS' align='center'> " + ( tempCustomer.getOperationsHold().trim().length() == 0 ? "APPROVED" : ( tempCustomer.getOperationsHold().equalsIgnoreCase( "0" ) ? "X": tempCustomer.getOperationsHold() ) ) + " </td>" );
                    sb.append( "<td class='NArialS' align='center'> " + ( tempCustomer.getSalesHold().trim().length() == 0 ? "APPROVED" : ( tempCustomer.getSalesHold().equalsIgnoreCase( "0" ) ? "X": tempCustomer.getSalesHold() ) ) + " </td></tr>" );

                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getMarketingHold(), ( tempCustomer.getMarketingHold().trim().length() == 0 ? 0 : 1 ), "MARKETING", user.getUserId(), lineItem.getLineItemId(), salesOrderId, null );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getFinanceHold(), ( tempCustomer.getFinanceHold().trim().length() == 0 ? 0 : 1 ), "FINANCE", user.getUserId(), lineItem.getLineItemId(), salesOrderId, null );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getSalesHold(), ( tempCustomer.getSalesHold().trim().length() == 0 ? 0 : 1 ), "SALES", user.getUserId(), lineItem.getLineItemId(), salesOrderId, null );
                    SalesDAO.saveHoldsLineItems( dbTransaction, tempCustomer.getOperationsHold(), ( tempCustomer.getOperationsHold().trim().length() == 0 ? 0 : 1 ), "OPERATIONS", user.getUserId(), lineItem.getLineItemId(),  salesOrderId, ( tempCustomer.getOperationsHold().trim().length() == 0 ? lineItem.getRequestDate() : null )  );
                    newLineItemsCount++;
                }
            }
            sb.append( "</table>" );
            if( newLineItemsCount == 0 && changeLineItemsCount  == 0)
            {
                sb = new StringBuffer();
                sb.append ( "" );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Exception while retreiving the customer holds", sqlex );
        }
        return sb.toString();
    }

    public static ArrayList getAllSalesPersonsByUserType( DBTransaction dbTransaction, User user ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList salesPersons = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
            {
                pstmt = conn.prepareStatement ( "SELECT Distinct SALESPERSON_NAME as Sales_Person FROM SALES_PERSONS WHERE SALESPERSON_NAME is not null and SALESPERSON_NAME != '' and SALESPERSON_ID = ?"  );
                pstmt.setInt ( 1, user.getSalesPersonId() );
                //salesPersons.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
            }
            else if (user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )
            {
                pstmt = conn.prepareStatement ( "select distinct SO_Sales_Person as Sales_Person from Sales_Order so, customer c where so.customer_id = c.customer_id and SO_Sales_Person is not null and SO_Sales_Person != '' and c.customer_id = ? \n" +
                            "union\n" +
                            "select distinct SalesPerson_Name as Sales_Person from customer c,CUSTOMER_SALESPERSON_RELATIONSHIP csr, SALES_PERSONS sp where c.customer_id = csr.customer_id and csr.salesperson_id = sp.salesperson_id and SalesPerson_Name is not null and SalesPerson_Name != '' and c.customer_id = ? "  );
                pstmt.setInt ( 1, user.getCustomerId() );
                pstmt.setInt ( 2, user.getCustomerId() );

            }
            else
            {
                pstmt = conn.prepareStatement("SELECT DISTINCT SO_Sales_Person as Sales_Person FROM Sales_Order where SO_Sales_Person is not null and SO_Sales_Person != '' and SO_Sales_Person <>''  order by SO_Sales_Person");
                //salesPersons.add(new org.apache.struts.util.LabelValueBean("All Sales Persons","0"));
            }
            rs = pstmt.executeQuery();
            while(rs.next())
            {
                salesPersons.add((rs.getString("Sales_Person")));
            }

        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Sales_Person from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return salesPersons;
   }
   public static ArrayList<LabelValueBean> getAllSalesPersonsListByUserType( DBTransaction dbTransaction, User user ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList<LabelValueBean> salesPersons = new ArrayList<LabelValueBean>();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
            {
                pstmt = conn.prepareStatement ( "SELECT Distinct SALESPERSON_NAME as Sales_Person FROM SALES_PERSONS WHERE SALESPERSON_NAME is not null and SALESPERSON_NAME != '' and SALESPERSON_ID = ?"  );
                pstmt.setInt ( 1, user.getSalesPersonId() );
                //salesPersons.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
            }
            else if (user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )
            {
                pstmt = conn.prepareStatement ( "select distinct SO_Sales_Person as Sales_Person from Sales_Order so, customer c where so.customer_id = c.customer_id and SO_Sales_Person is not null and SO_Sales_Person != '' and c.customer_id = ? \n" +
                            "union\n" +
                            "select distinct SalesPerson_Name as Sales_Person from customer c,CUSTOMER_SALESPERSON_RELATIONSHIP csr, SALES_PERSONS sp where c.customer_id = csr.customer_id and csr.salesperson_id = sp.salesperson_id and SalesPerson_Name is not null and SalesPerson_Name != '' and c.customer_id = ? "  );
                pstmt.setInt ( 1, user.getCustomerId() );
                pstmt.setInt ( 2, user.getCustomerId() );

            }
            else
            {
                pstmt = conn.prepareStatement("SELECT DISTINCT SO_Sales_Person as Sales_Person FROM Sales_Order where SO_Sales_Person is not null and SO_Sales_Person != '' and SO_Sales_Person <>''  order by SO_Sales_Person");
                //salesPersons.add(new org.apache.struts.util.LabelValueBean("All Sales Persons","0"));
            }
            rs = pstmt.executeQuery();
            while(rs.next())
            {
                salesPersons.add(new LabelValueBean(rs.getString("Sales_Person"),rs.getString("Sales_Person") ));
            }

        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Sales_Person from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return salesPersons;
   }

    public static ArrayList loadSaleOrdersStatus()
    {
        ArrayList statusList = new ArrayList();

        statusList.add( new LabelValueBean("APPROVED","APPROVED"));
        statusList.add( new LabelValueBean("HOLD","HOLD"));
        statusList.add( new LabelValueBean("SHIPPED","SHIPPED"));

        return  statusList;
    }

    public static ArrayList loadAllSalesOrderIds(int isSample) throws ScalarException
    {

            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            ArrayList salesOrderIds = new ArrayList();

            try
            {
                conn = DataSource.getConnection();
                //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAllSalesOrderIdAndNumbers"));
                pstmt = conn.prepareStatement ( "SELECT SalesOrder_ID,SalesOrder_No FROM Sales_Order WHERE So_Is_Sample = ? ORDER BY SalesOrder_No desc " );
                pstmt.setInt(1, isSample);
                rs = pstmt.executeQuery();
                salesOrderIds.add( new LabelValueBean( "All Sales Orders", "0" ));
                while (rs.next())
                {
                    salesOrderIds.add( new LabelValueBean(rs.getString("SalesOrder_NO"), rs.getString("SalesOrder_NO"))); ;
                }
            }
            catch (Exception sqlex)
            {
                sqlex.printStackTrace();
                throw new ScalarException ("Could not get SalesOrder Info from database", sqlex);
            }
            finally
            {
                if (rs != null) {
                    try { rs.close(); } catch (SQLException excp) {}
                }
                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException excp) {}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException excp) {}
                }
            }

            return salesOrderIds;
       }
    public static ArrayList loadAllCustomerPOs(int isSample, String salesOrderType ) throws ScalarException
        {

            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            ArrayList salesOrderIds = new ArrayList();

            try
            {
                conn = DataSource.getConnection();
                if ("MissedOrders".equalsIgnoreCase( salesOrderType))
                {
                    pstmt = conn.prepareStatement ( "SELECT DISTINCT SO_Customer_PO_Number,Sales_Order.SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE  Sales_Order.SalesOrder_Id = Customer_Open_Order_LineItem.SalesOrder_ID and COOL_Status <> 'SHIPPED' and " +  loadSearchString ( isSample, salesOrderType ) );
                }
                else if ("All".equalsIgnoreCase(salesOrderType))
                {
                    pstmt = conn.prepareStatement ( "SELECT DISTINCT SO_Customer_PO_Number, Sales_Order.SalesOrder_ID, Sales_Order.SalesOrder_No FROM Sales_Order,Customer_Open_Order_LineItem WHERE Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID and " + loadSearchString(isSample, salesOrderType) );
                }
                else if ("PackingSlipSalesOrders".equalsIgnoreCase(salesOrderType))
                {
                    pstmt = conn.prepareStatement ( "SELECT distinct SO_Customer_PO_Number, Sales_Order.SalesOrder_ID, Sales_Order.SalesOrder_No FROM Sales_Order,Customer_Open_Order_LineItem WHERE Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID ORDER BY SO_Customer_PO_Number desc"  );
                }
                else
                {
                    pstmt = conn.prepareStatement ( "SELECT DISTINCT SO_Customer_PO_Number, SalesOrder_No FROM Sales_Order WHERE  " + loadSearchString(isSample, salesOrderType) );
                }
                //pstmt.setInt(1, isSample);
                //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAllSalesOrderIdAndCustomerPOs"));
                rs = pstmt.executeQuery();
                String poNumber;
                salesOrderIds.add( new LabelValueBean( "All Purchase Orders", "0" ));
                while (rs.next())
                {
                    poNumber = rs.getString ("SO_Customer_PO_Number");
                    if ( poNumber.trim() != null && poNumber.trim()!="" )
                    {
                        salesOrderIds.add( new LabelValueBean( poNumber, poNumber ));
                    }
                }
            }
            catch (Exception sqlex)
            {
                sqlex.printStackTrace();
                throw new ScalarException ("Could not get SalesOrder Info from database", sqlex);
            }
            finally
            {
                if (rs != null) {
                    try { rs.close(); } catch (SQLException excp) {}
                }
                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException excp) {}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException excp) {}
                }
            }

            return salesOrderIds;
       }

    public static ArrayList loadAllSalesOrderIds( int isSample, String salesOrderType ) throws ScalarException
    {

            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            ArrayList salesOrderIds = new ArrayList();

            try
            {
                conn = DataSource.getConnection();
                //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAllSalesOrderIdAndNumbers"));
                if ("MissedOrders".equalsIgnoreCase( salesOrderType))
                {
                    pstmt = conn.prepareStatement ( "SELECT DISTINCT Sales_Order.SalesOrder_ID,SO_Customer_PO_Number,SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE  Sales_Order.SalesOrder_Id = Customer_Open_Order_LineItem.SalesOrder_ID and COOL_Status <> 'SHIPPED' and " +  loadSearchString ( isSample, salesOrderType ) );
                }
                else if ("All".equalsIgnoreCase(salesOrderType))
                {
                    pstmt = conn.prepareStatement ( "SELECT distinct Sales_Order.SalesOrder_ID, Sales_Order.SalesOrder_No FROM Sales_Order,Customer_Open_Order_LineItem WHERE Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID and " + loadSearchString(isSample, salesOrderType) );
                }
                else if ("PackingSlipSalesOrders".equalsIgnoreCase(salesOrderType))
                {
                    pstmt = conn.prepareStatement ( "SELECT distinct Sales_Order.SalesOrder_ID, Sales_Order.SalesOrder_No FROM Sales_Order,Customer_Open_Order_LineItem WHERE Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID ORDER BY SalesOrder_No desc"  );
                }
                else
                {
                    pstmt = conn.prepareStatement ( "SELECT SalesOrder_ID, SalesOrder_No FROM Sales_Order WHERE " + loadSearchString(isSample, salesOrderType) );
                }
                rs = pstmt.executeQuery();
                salesOrderIds.add( new LabelValueBean( "All Sales Orders", "0" ));
                while (rs.next())
                {
                    salesOrderIds.add( new LabelValueBean(rs.getString("SalesOrder_NO"), rs.getString("SalesOrder_NO"))); ;
                }
            }
            catch (Exception sqlex)
            {
                sqlex.printStackTrace();
                throw new ScalarException ("Could not get SalesOrder Info from database", sqlex);
            }
            finally
            {
                if (rs != null) {
                    try { rs.close(); } catch (SQLException excp) {}
                }
                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException excp) {}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException excp) {}
                }
            }

            return salesOrderIds;
       }
    private static String loadSearchString(int isSample, String searchType )
                                 throws ScalarException
        {
            String search_string = "";
            if(isSample == 0)
                search_string = " So_Is_Sample = 0";
            else if(isSample == 1)
                search_string = " So_Is_Sample = 1";
            if ("PendingSalesOrders".equalsIgnoreCase(searchType))
            {
                search_string = search_string +  " and SO_Is_Pending = 0";
            }
            else if ("MissedOrders".equalsIgnoreCase(searchType))
            {
                search_string = search_string + " and ISNULL( COOL_Revised_Date, COOL_Schedule_Date ) < CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101 )";
            }
            else if ("CancelOrders".equalsIgnoreCase(searchType))
            {
                search_string = search_string + " and SO_Is_Cancel = 0";
            }

             return search_string + " ORDER BY SalesOrder_No desc";

        }


}